BookmarkSubscribeRSS Feed
soham_sas
Quartz | Level 8

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

 

 

3 REPLIES 3
Astounding
PROC Star

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_
Rhodochrosite | Level 12

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

 

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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