topic Re: How to get ONLY the five maximum obs. within each month in SAS Programming

How to get ONLY the five maximum obs. within each month

ZZB — Thu, 09 Nov 2017 11:44:43 GMT

For each ID, it has year, month, and day observations.

I want to get the five largest numbers within each month for each ID. How to get it?
I can use "proc sort; by ID year month obs" to rank the obs within each month, but I do not know how to get only the five largest numbers.

Thanks!

Re: How to get ONLY the five maximum obs. within each month

Kurt_Bremser — Thu, 09 Nov 2017 11:55:00 GMT

Sort

by id year month descending obs;

and then retrieve the first 5 observations

by id year month;

Re: How to get ONLY the five maximum obs. within each month

ZZB — Thu, 09 Nov 2017 12:10:14 GMT

Thanks! Any additional code to retrieve the first 5 observations?

proc sort data=max2 obs=5; /* it does not work */
by permno year month;
run;

Re: How to get ONLY the five maximum obs. within each month

Kurt_Bremser — Thu, 09 Nov 2017 12:15:15 GMT

You have to do a count within each by group:

proc sort data=max2;
by permno year month descending obs;
run;

data want;
set max2;
by permno year month;
retain counter;
if first.month
then counter = 1;
else counter + 1;
if counter le 5;
drop counter;
run;

assuming that "obs" is a variable already present in the dataset. If you need to create that for each day, it can be done similar to the above code, or by using proc freq.