Quartz | Level 8

## Select top 3 records

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

3 REPLIES 3
PROC Star

## Re: Select top 3 records

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;

Rhodochrosite | Level 12

## Re: Select top 3 records

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;``````
Tourmaline | Level 20

## Re: Select top 3 records

``````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;``````

Discussion stats
• 3 replies
• 4892 views
• 2 likes
• 4 in conversation