turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Finding maximum value in a column within a subset/...

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
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-08-2012 02:01 PM

Hello,

I have a data similar to the one below, but without the max3_value column. This is the one I would like to find, but I do not know how.

max3_value takes the last 3 values of the value column (within a group of person) and finds the maximum value of them. Of course, if it is the first or the second observation, it outputs a missing value.

For example: max3_value=10 in period 3 for person A is calculated as a max(10,5,3).

I thought of transposing the data with lagged values, but still I do not know how calculate it within groups of person.

I would kindly appreciate any help.

person | period | value | max3_value |

A | 1 | 10 | . |

A | 2 | 5 | . |

A | 3 | 3 | 10 |

A | 4 | 5 | 5 |

A | 5 | 2 | 5 |

A | 6 | 3 | 5 |

A | 7 | 4 | 4 |

A | 8 | 14 | 14 |

B | 1 | 6 | . |

B | 2 | 5 | . |

B | 3 | 1 | 6 |

B | 4 | 1 | 4 |

B | 5 | 4 | 4 |

Accepted Solutions

Solution

12-08-2012
02:54 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-08-2012 02:54 PM

One way is populate variables with the previous 2 values and keep count of how many obs you have seen for this person.

data want;

set have;

by person ;

lag1=lag1(value);

lag2=lag2(value);

if first.person then cnt=0;

cnt + 1;

if cnt < 3 then max3=.;

else max3 = max(lag1,lag2,value);

run;

All Replies

Solution

12-08-2012
02:54 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-08-2012 02:54 PM

One way is populate variables with the previous 2 values and keep count of how many obs you have seen for this person.

data want;

set have;

by person ;

lag1=lag1(value);

lag2=lag2(value);

if first.person then cnt=0;

cnt + 1;

if cnt < 3 then max3=.;

else max3 = max(lag1,lag2,value);

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-08-2012 03:32 PM

Thank you for prompt response, it works perfectly.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-08-2012 02:59 PM

Why is it obvious that the first two values will be missing? Do you always want them to be?

Also, why is the next to the last value for person A assigned the value 4? Was that supposed to be 14?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-08-2012 03:10 PM

You are right, this is not obvious, but that is the way I want them to be (it makes sense in the original dataset).

max3_value=4 is OK, as it is a max(2,3,4).

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-08-2012 03:27 PM

Tom's suggested code appears to accomplish what you want.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-08-2012 03:28 PM

Actually it should be 5 as the max of 5,1,1 is 5.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-08-2012 06:28 PM