DATA Step, Macro, Functions and more

Select top N data

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Select top N data

Hello,

I am so new to SAS progam and need help with my codes below.  I am trying to find the top 10% of employees with highest salary.

DATA TOP10PS; SET SAS.T;

PROC SORT; BY DESCENDING SALARY;

DATA EE1; SET TOP10PS END=EOF;

IF EOF THEN DO;

FORMAT TENP 7.;

COUNT=_N_;

TENP = COUNT / 10;

IF COUNT <= TENP THEN OUTPUT;

END;

KEEP EE RACE SEX SALARY;

RUN;

DATA FINAL; SET EE1;

PROC PRINT N; ID EE; VAR RACE SEX SALARY;

RUN;


Accepted Solutions
Solution
‎02-15-2017 12:08 PM
PROC Star
Posts: 7,366

Re: Select top N data

Your code wouldn't be recognized, by SAS, as being valid code. Conversely, the following would do what you want.

When you run a querry using SAS proc sql, &sqlobs. contains the number of records in the file:

 

/*as an example I'm substituting sashelp.class for sas.t*/
proc sql noprint;
  create table final as
    select *
      from sashelp.class (rename=(name=EE
                                  age=RACE
                                  weight=SALARY))
        order by salary descending
  ;
quit;

data final (keep=EE RACE SEX SALARY);
  set final;
  if _n_/&sqlobs. gt .1 then stop;
  output;
run;

proc print;
run;

HTH,

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
PROC Star
Posts: 554

Re: Select top N data

Solution
‎02-15-2017 12:08 PM
PROC Star
Posts: 7,366

Re: Select top N data

Your code wouldn't be recognized, by SAS, as being valid code. Conversely, the following would do what you want.

When you run a querry using SAS proc sql, &sqlobs. contains the number of records in the file:

 

/*as an example I'm substituting sashelp.class for sas.t*/
proc sql noprint;
  create table final as
    select *
      from sashelp.class (rename=(name=EE
                                  age=RACE
                                  weight=SALARY))
        order by salary descending
  ;
quit;

data final (keep=EE RACE SEX SALARY);
  set final;
  if _n_/&sqlobs. gt .1 then stop;
  output;
run;

proc print;
run;

HTH,

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 30

Re: Select top N data

Hello Art,
Thank you so much and very grateful for your assistance. ~Bonnary
Valued Guide
Posts: 797

Re: Select top N data

You could use proc rank to get the top decile first.  Then you can sort that much smaller data set without further filtering. 

 

proc rank data=sas.t 
                out=final (where=(salary_decile=9)) 
                groups=10;
  var salary;
  ranks salary_decile;
run;

proc sort;
  by descending salary;
run;

proc print n;
  id ee;
  var race sex salary;
run;

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 309 views
  • 0 likes
  • 4 in conversation