BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SeaMoon_168
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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)) ...

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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)) ...
Ksharp
Super User

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 827 views
  • 1 like
  • 3 in conversation