Solved
Contributor
Posts: 43

# 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: 9,925

## 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
How to convert datasets to data steps
How to post code

All Replies
Super User
Posts: 9,925

## 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
How to convert datasets to data steps
How to post code
Contributor
Posts: 43

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

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: 9,925

## 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
How to convert datasets to data steps
How to post code
☑ This topic is solved.