Getting top two values (counts of categories) by id in proc sql

[ConsAth]

Hi all,

What I am trying to achieve is the following :

I am trying to count the categories by the ID i have and to keep only the top  two  frequent for every ID.

I have tried

count distinct categories grouping by ID with a proc sql statement  by I get stuck on the next step where i want to keep the top 2 by every ID, for example if i just wanted the top value per ID i would take the max but how does that work for getting the second max value as well?

Kind regards and thank you in advance

[ballardw · Accepted solution]

One way, not necessarily the slickest but relatively easy to understand.

Proc freq data=have order=freq noprint;
   table id*categoricalvar/ out=temp;
run;

data want;
   set temp;
   by id;
   retain counter;
   if first.id then counter=1;
   else counter+1;
   if counter le 2 then output;
run;

[ConsAth]

Very nice and effective than sql and for sure easier to understand thank you very much