I would select the top 2 items for two groups. When the 2nd item is equal to the 3rd and 4th I'd like those included. The data is shown below.
ID Count A 80 A 25 A 25 A 25 A 20 B 70 B 30 B 10 B 9 B 7
It would be
ID Count A 80 A 25 A 25 A 25 B 70 B 30
That is what PROC RANK is for. You want the TIES=DENSE option.
First let's convert your listing back into an actual dataset so we have something to program against.
data have;
input ID $ Count;
cards;
A 80
B 25
C 25
D 25
E 20
;
Now you make a new RANK variable.
proc rank data=have out=want ties=dense ;
var count;
ranks rank ;
run;
You could then subset the results later
proc print data=want;
where rank <= 2 ;
run;
Or if you know you don't care about the other ranks subset it on the way out of the PROC RANK step by using the WHERE= dataset option.
... out=want(where=(rand<=2)) ...
That is what PROC RANK is for. You want the TIES=DENSE option.
First let's convert your listing back into an actual dataset so we have something to program against.
data have;
input ID $ Count;
cards;
A 80
B 25
C 25
D 25
E 20
;
Now you make a new RANK variable.
proc rank data=have out=want ties=dense ;
var count;
ranks rank ;
run;
You could then subset the results later
proc print data=want;
where rank <= 2 ;
run;
Or if you know you don't care about the other ranks subset it on the way out of the PROC RANK step by using the WHERE= dataset option.
... out=want(where=(rand<=2)) ...
Here is PROC SQL solution if you want it and your dataset is not big.
data have;
input ID $ Count ;
cards;
A 80
A 25
A 25
A 25
A 20
B 70
B 30
B 10
B 9
B 7
;
proc sql;
create table want as
select * from have as c where exists(select * from
(
select a.id,a.count
from have as a,have as b
where a.id=b.id and a.count<=b.count
group by a.id,a.count
having count(distinct b.count)<3
) as d
where c.id=d.id and c.count=d.count
)
order by 1,2 desc;
quit;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.