topic Determine Date of Maximum Value by Year in Discussions

Determine Date of Maximum Value by Year

terapin — Sat, 10 Mar 2012 17:48:37 GMT

Hi Folks,

I have a dataset that includes daily snow depth data for a 20 year time period. I need to determine the date by year when the peak, or maximum, snow depth value occurred. Any suggestions on how to accomplish this? Thanks.

Determine Date of Maximum Value by Year

pgstats — Sat, 10 Mar 2012 18:53:20 GMT

you may get many dates which share the maximum depth... This is how to get them:

proc sql;

create table peaks as

select year(date) as year, date, depth

from myData

group by calculated year

having depth = max(depth);

select * fron peaks;

quit;

Re: Determine Date of Maximum Value by Year

hai_kuo — Sat, 10 Mar 2012 18:54:48 GMT

There would be many ways to tackle your problem. To start, let 's take sashelp.class as an example, using 'sex' as the equavalent to your 'year', 'height' to your 'snow depth':

1. SQL solution:

proc sql;

create table want as

select a.* from sashelp.class a,

(select sex, max(height) as max from sashelp.class group by sex) b

where a.sex=b.sex and a.height=b.max

;

quit;

2. Data step;

proc sort data=sashelp.class out=tem;

by sex;

run;

data want (drop=_h);

do until (last.sex);

set tem;

by sex;

_h=max(_h,height);

end;

do until (last.sex);

set tem;

by sex;

if height=_h then output;

end;

run;

And of course, there are many other ways , such as proc summary etc, worth looking into:

proc summary data=sashelp.class nway;

class sex;

var height;

output out=want (drop=_:) maxid(height(name) height(age) height(weight))=name age weight max= ;

run;

Regards,

Haikuo

Determine Date of Maximum Value by Year

Sat, 10 Mar 2012 19:16:33 GMT

Somehow I think that the OP was looking for a solution in JMP. While I am not versed in JMP, the following might provide what you are looking for: http://communities.sas.com/thread/14196

Determine Date of Maximum Value by Year

pgstats — Sat, 10 Mar 2012 20:58:02 GMT

Art is probably right, the question is about solving this in JMP. As an occasional user I would do:

Create a YEAR column
Use tabulate to create a new table of yearly maximum depths (called, say, maxDepth)
Join that table with the original by matching YEAR, include variables Date, YEAR, depth and maxDepth
Create a new column with formula depth==maxDepth, call it Equal
Sort the table by Equal (decreasing) and Date - the needed dates will be on top.

Determine Date of Maximum Value by Year

pmroz — Mon, 12 Mar 2012 13:07:17 GMT

Hi terapin,

To do this in JMP you can use the col maximum function followed by get rows where. Here's an example where the maximum snowfall occurred in two different years:

dt = New Table( "Snowfall", Add Rows( 20 ),

New Column( "Snow Year", Numeric, Continuous, Format( "Best", 12 ),

Set Values(

[1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969, 1970, 1971,

1972, 1973, 1974, 1975, 1976, 1977, 1978, 1979])),

New Column( "Snow Depth", Numeric, Continuous, Format( "Best", 12 ),

Set Values(

[34, 53, 99, 12, 53, 77, 48, 73, 12, 6, 44, 99, 58, 98, 57, 45, 34, 23,

76, 81])),

Set Row States( [0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] )

);

max_depth = col maximum(:Snow Depth);

max_rows = dt << get rows where(:Snow Depth == max_depth);

for (i = 1, i <= nrows(max_rows), i++,

k = max_rows[i];

print("Maximum snowfall of " || char(max_depth) || " in " || char(:Snow Year[k]));

);

Determine Date of Maximum Value by Year

terapin — Mon, 12 Mar 2012 22:06:23 GMT

Well,

I may not have been as clear as I needed to be about this. The ideas are helpful, but don't quite help me solve the issue. PMroz's last example is close, but won't allow me to determine the maximum value for each year where each year has 365 daily observations. That is, I'm looking for something like

max_depth = col maximum(:Snow Depth), By :Year;

With the rows identified above, I would then like to select each of the rows within each year that has the maximum value. This would allow me to subset the selected data, grabbing both the year, date, and maximum snow depth value.

Is it possible to use a By statement as part of the col maximum function?

Re: Determine Date of Maximum Value by Year

ms — Tue, 13 Mar 2012 07:47:37 GMT

Yes, use

max_depth = col maximum(:Snow Depth, :Year)

Note that :Year must be character formatted.

Edit: PMroz is right below. Sorry if my post was misleading.

Determine Date of Maximum Value by Year

pmroz — Tue, 13 Mar 2012 15:10:55 GMT

The col maximum function only takes a second argument when used in a column formula or with for all rows().

The summarize command can do what you want:

summarize(year_list=by(:Snow Year), max_depth_list=max(:Snow Depth));

Determine Date of Maximum Value by Year

terapin — Tue, 13 Mar 2012 17:58:33 GMT

PMroz,

This helps get me closer, but I can't see how I grab the actual date of the year when the maximum snow depth value was found? Ultimately, I need to determine for each year both the maximum snow depth value and the date that it occurred. Like the Tables Summary command, all I'm getting is the max value and year, not the date. I don't see an option in either Tables Summary or Summarize that lets me grab data from other columns. Do you have any suggestions on how to accomplish that? Thanks

Re: Determine Date of Maximum Value by Year

mpb — Tue, 13 Mar 2012 19:49:09 GMT

The general idea was outlined by PGstats above and also in the link provided by art297 above. Here's an example assuming you have table whose columns are Year, Day, and Depth. Day is a number from 1 to 365 or 366. It could instead be the actual date. In either case try out the following code. I tested it on a very small table with 3 years and 4 days per year and arbitrary depths (except I made sure there were multiple days with the max depth for each year. If you do not want to alter your main table you can replace the Table>Update command with the Table>Join command in which case you would not choose the option to update the source table.

dt = Data Table( "Demo Max Depth" );

dtMax = dt << Summary( Group( :Year ), Max( :Depth ) );

dt << Update(

With( dtMax ),

Match Columns( :Year = :Year ),

Add Columns from Update table( :Name( "Max(Depth)" ) )

);

dt << select where( :Depth == :Name( "Max(Depth)" ) );

dtDaysMax = dt << Subset( Selected Rows( 1 ) )

Determine Date of Maximum Value by Year

pmroz — Tue, 13 Mar 2012 20:18:45 GMT

The approach outlined by mpb is excellent for adding the maximum depth to the original table. I've modified my original example to print out the maximum depth and date from the table. I'm assuming that you have the fields Snow Date (date type) and Snow Depth (number). I've added a column formula for a year column.

dt = New Table( "Snowfall",

Add Rows( 20 ),

New Column( "Snow Depth", Numeric, Continuous, Format( "Best", 12 ),

Set Values(

[34, 45, 53, 56, 99, 67, 12, 78, 53, 89, 77, 90, 48, 12, 73, 23, 12, 34,

6, 45] ) ),

New Column( "Snow Date", Numeric, Continuous, Format( "ddMonyyyy", 10 ),

Input Format( "ddMonyyyy" ),

Set Values(

[1767225600, 1771113600, 1805155200, 1809129600, 1843084800, 1838160000,

1889049600, 1893283200, 1896220800, 1900281600, 1950566400, 1955491200,

1957651200, 1964217600, 1990396800, 1995926400, 2023488000, 2029449600,

2051481600, 2056752000] ) ),

New Column( "Snow Year", Numeric, Continuous, Format( "Best", 12 ),

Formula( Year( :Snow Date ) ),

Lock( 1 )

)

);

// Get the highest snowfall for each year

summarize(year_list=by(:Snow Year), max_depth_list=max(:Snow Depth));

// Now get the dates of the highest snowfall

for (i = 1, i <= nrows(max_depth_list), i++,

one_year = year_list[i];

one_depth = max_depth_list[i];

// Could have the same maximum snow depth on different days

one_year_max_rows = dt << get rows where(:Snow Year == num(one_year) &

:Snow Depth == one_depth);

for (k = 1, k <= nrows(one_year_max_rows), k++,

m = one_year_max_rows[k];

snow_date = :Snow Date[m];

print("Maximum snowfall of " || char(one_depth) || " on " ||

format(snow_date, "ddMonyyyy"));

);

Determine Date of Maximum Value by Year

terapin — Tue, 20 Mar 2012 18:22:27 GMT

Thanks PMroz for your helpful suggestion. Your code did the trick and taught me a lot about how to solve these types of issues in the future. Thanks everyone else for helping move the discussion along and offering interesting insights.