Select top 3 records

[soham_sas]

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

[Astounding]

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;

[kiranv_]

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;

[novinosrin]

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;