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 |
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.