topic Re: Select top N data in SAS Programming

Select top N data

BonnaryW — Tue, 14 Feb 2017 22:04:00 GMT

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;

Re: Select top N data

PeterClemmensen — Tue, 14 Feb 2017 22:13:05 GMT

Check out this thread 🙂

https://communities.sas.com/t5/General-SAS-Programming/choose-top-10-percent-data-based-on-one-variable/td-p/272003

Re: Select top N data

art297 — Tue, 14 Feb 2017 22:34:49 GMT

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

Re: Select top N data

BonnaryW — Tue, 14 Feb 2017 22:46:58 GMT

Hello Art,
Thank you so much and very grateful for your assistance. ~Bonnary

Re: Select top N data

mkeintz — Wed, 15 Feb 2017 01:32:57 GMT

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;