topic Re: Select top 3 records in SAS Programming

Select top 3 records

soham_sas — Thu, 05 Apr 2018 13:45:41 GMT

HI ,

i have data set like below , where the employee id is repeating and the salary for the repeated id is also same

i want to take top 3 salary from the table , and if the id is repeated then i want all the repeated rows also e.g emp 1 and 2 is repeated in original table so in result also they are repeated .

data emp;
input id sal;
cards;
1 10000
2 20000
3 30000
1 10000
2 20000
4 5000
5 4000
;
run;

data want
ID	SAL
1	10000
1	10000
2	20000
2	20000
3	30000

Re: Select top 3 records

Astounding — Thu, 05 Apr 2018 14:05:02 GMT

One way:

proc sort data=have;

by descending sal id;

run;

data want;

set have;

by id notsorted;

output;

if last.id then count + 1;

if count=3 then stop;

drop count;

run;

Optionally, you might want to re-sort:

proc sort data=want;

by id;

run;

Re: Select top 3 records

kiranv_ — Thu, 05 Apr 2018 15:01:23 GMT

2 more ways to do this

By Plain SQL

proc sql;
create table have(drop = rank) as 
select id, sal,
(select count(distinct sal) from emp b
where a.sal<=b.sal)as rank
from emp a
where calculated rank le 3
order by sal;

By using proc rank and using the rank and then sort

proc rank data= emp out=emp_rank descending  ties=dense;
var sal;
   ranks sal_rank;
run;



proc sort data=emp_rank(where =(sal_rank le 3)) out=want(drop=sal_rank);
by id;
run;

Re: Select top 3 records

novinosrin — Thu, 05 Apr 2018 18:59:25 GMT

data emp;
input id sal;
cards;
1 10000
2 20000
3 30000
1 10000
2 20000
4 5000
5 4000
;
run;

data want;
if _n_=1 then do;
if 0 then set emp;
   dcl hash H (dataset:'emp(obs=3)',ordered: "d") ;
   h.definekey  ("sal") ;
   h.definedone () ;
end;
set emp;
if h.find()=0 then output;
run;