Determine Date of Maximum Value by Year

[#1]

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.

[#2]

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;

PG

[#3]

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

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

PG

[#4]

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

[#5]

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

[#6]

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]));

);

[#7]

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?

[#8]

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.

[#9]

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));

[#10]

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