topic Re: Extracting top 20 - 30 observations group wise from SAS dataset in Statistical Procedures

Extracting top 20 - 30 observations group wise from SAS dataset

AkshayjitSinghNarula — Sat, 18 Jun 2016 10:08:04 GMT

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? 🙂

Re: Extracting top 20 - 30 observations group wise from SAS dataset

jklaverstijn — Sat, 18 Jun 2016 10:49:33 GMT

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.

Re: Extracting top 20 - 30 observations group wise from SAS dataset

Reeza — Sat, 18 Jun 2016 13:12:19 GMT

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;