topic proc sql or other ways to select top 2 or more if there are more items sharing the 2nd highest value in SAS Programming

proc sql or other ways to select top 2 or more if there are more items sharing the 2nd highest value

SeaMoon_168 — Sat, 07 Dec 2024 02:54:08 GMT

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

Re: proc sql or other ways to select top 2 or more if there are more items sharing the 2nd highest v

Tom — Sat, 07 Dec 2024 02:37:27 GMT

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

Re: proc sql or other ways to select top 2 or more if there are more items sharing the 2nd highest v

Ksharp — Sat, 07 Dec 2024 07:07:16 GMT

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;