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

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

ConsAth — Mon, 19 Sep 2016 19:56:49 GMT

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

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

ballardw — Mon, 19 Sep 2016 20:08:33 GMT

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;

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

ConsAth — Mon, 19 Sep 2016 20:33:53 GMT

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