Programming the statistical procedures from SAS

Extracting top 20 - 30 observations group wise from SAS dataset

Reply
New Contributor
Posts: 4

Extracting top 20 - 30 observations group wise from SAS dataset

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? Smiley Happy

Super Contributor
Posts: 409

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

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.

Super User
Posts: 18,576

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

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;
Ask a Question
Discussion stats
  • 2 replies
  • 363 views
  • 2 likes
  • 3 in conversation