turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Determine Date of Maximum Value by Year

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 10, 2012 9:48 AM
(3808 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

PG

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

**)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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