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

Accepted Solution Solved
Reply
Contributor ZZB
Contributor
Posts: 43
Accepted Solution

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

[ Edited ]

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!


Accepted Solutions
Solution
‎11-09-2017 07:30 AM
Super User
Posts: 8,590

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 8,590

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

Sort

by id year month descending obs;

and then retrieve the first 5 observations

by id year month;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor ZZB
Contributor
Posts: 43

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

Posted in reply to KurtBremser

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;

Solution
‎11-09-2017 07:30 AM
Super User
Posts: 8,590

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 69 views
  • 0 likes
  • 2 in conversation