DATA Step, Macro, Functions and more

Select top 3 records

Reply
Contributor
Posts: 40

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
IDSAL
110000
110000
220000
220000
330000

 

 

Super User
Posts: 6,626

Re: Select top 3 records

Posted in reply to soham_sas

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;

PROC Star
Posts: 503

Re: Select top 3 records

Posted in reply to soham_sas

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;
PROC Star
Posts: 1,558

Re: Select top 3 records

[ Edited ]
Posted in reply to soham_sas
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;

 

Ask a Question
Discussion stats
  • 3 replies
  • 101 views
  • 2 likes
  • 4 in conversation