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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: