Extracting top 20 - 30 observations group wise from SAS dataset

[AkshayjitSinghNarula]

Hi,

I've been struggling to figure out a way to extract the top 20-30 observations, group wise, from a SAS dataset I'm working  on. 

Basically, I have 5 Indian cities in my dataset (Variable = City) and Net sales against each customer name. I want to pull out the top 20 - 30 observations based on the largest net sales values for each city automatically and subsequently create a separate dataset of those observations. Can someone help me here please? 🙂

[jklaverstijn]

There is some sample code in SAS note 24778 that explains how to do it in datastep. Depending on how you'd like to deal with ties (equal scores) PROC RANK may also be helpful:

proc sort data=sashelp.cars out=cars;
        by make;
run;
proc rank data=sashelp.cars ou=carsrank;
        by make;
        var horsepower;
        ranks hp_r;
run;
proc sort data=carsrank;
        by make hp_r;
        where hp_r <= 5;
run;

Actually, Google on "sas top n by group" gives a myriad of alternatives, each with its own virtues.

Good luck,

- Jan.

[Reeza]

1. Sort dataset by group and variable of interest

2. Add a counter to count the values

3. Take top N as desired

*Assuming N = 5, take top 5 tallest students by sex; 

*1 - sort by variables;
proc sort data=sashelp.class out=class;
by sex descending height;
run;


data want;
set class;
by sex;

*Create counter for each group;
if first.sex then count=1;
else count+1;

*Extract based on N;
if count<5;

run;