Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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

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


Accepted Solutions
Solution
‎09-19-2016 04:33 PM
Super User
Posts: 11,343

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

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;

View solution in original post


All Replies
Solution
‎09-19-2016 04:33 PM
Super User
Posts: 11,343

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

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;
Occasional Contributor
Posts: 11

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

Very nice and effective than sql and for sure easier to understand thank you very much
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 231 views
  • 1 like
  • 2 in conversation