Subscribe Bookmark RSS Feed

Determine Date of Maximum Value by Year

terapin

Community Trekker

Joined:

Jun 23, 2011

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

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

    );

);

12 REPLIES
pgstats

Community Trekker

Joined:

Aug 30, 2011

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

pgstats

Community Trekker

Joined:

Aug 30, 2011

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

hai_kuo

Community Trekker

Joined:

Jan 25, 2012

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

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

pmroz

Super User

Joined:

Jun 23, 2011

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

);

terapin

Community Trekker

Joined:

Jun 23, 2011

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?

ms

Super User

Joined:

Jun 23, 2011

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.

pmroz

Super User

Joined:

Jun 23, 2011

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

terapin

Community Trekker

Joined:

Jun 23, 2011

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