Creating a table of highest n values

[crawfe]

Hi. I am somewhat new to SAS. I an sure this is simple but I am stuck.

I have a table of CODES and a count by CODE. I would like to create a

smaller table of just the Top n codes (3 in this example).

Could you provide an example code? Thanks!

HAVE (code_tbl):                                                WANT (tbl_topthree):

CODE      COUNT_OF_CODES        CODE      COUNT_OF_CODES 

123                   6                                   126                      10

124                   8                                  136                       9

126                   10                                124                       8

236                    4

324                    7

223                    2

136                    9

etc.                    etc.

(Is it also possible to do one level deeper? Such as if I had

STORE   CODE    COUNT_OF_CODES and wanted the top 3 COUNT by each

STORE.)

[novinosrin]

Hi @crawfe  PROC RANK is likely what you need. Well, no point in me copying examples straight from online search and pasting it here. All the very best!

Please take a look at 

Example 2: Ranking Values within BY Groups

Here is the link https://documentation.sas.com/?docsetId=proc&docsetTarget=n1hxon9vm350ikn19oeualfap8qy.htm&docsetVer...

[crawfe]

Thanks. I will study that.

[Patrick]

Something like below might work.

data have(drop=_:);
  do store=1 to 5;
    do code=1 to 5;
      _stop=rand('integer',1,100);
      do _i=1 to _stop;
        output;
      end;
    end;
  end;
  stop;
run;

proc sql;
  create table inter as
    select store, code, count(*) as cnt
    from have
    group by store, code 
    ;
quit;

proc rank data=inter ties=dense descending
  out=want(where=(rank<=3));
  by store;
  var cnt;
  ranks rank;
run;

proc print data=want;
run;

[Watts]

proc freq data=have order=freq;
     tables code / maxlevels=3        nopct nocum;
     weight count_of_codes;
 run;

[crawfe]

Thanks. I will try this for the future. I knew it would be more complicated.

There didn't seem to be a simple, multi-level function (such as for Group by).

[Rick_SAS]

Use the MAXLEVELS= option in PROC FREQ, as discussed in the blog post "An easy way to make a 'Top 10' table and bar chart in SAS"

[crawfe]

Thanks. I will try it.

[Reeza · Accepted solution]

proc sql outobs=3;
create table want as
select code, freq
from have
order by freq desc;
quit;

SQL is pretty straight forward.

---

@crawfe wrote:

Hi. I am somewhat new to SAS. I an sure this is simple but I am stuck.

I have a table of CODES and a count by CODE. I would like to create a

smaller table of just the Top n codes (3 in this example).

Could you provide an example code? Thanks!

HAVE (code_tbl):                                                WANT (tbl_topthree):

CODE      COUNT_OF_CODES        CODE      COUNT_OF_CODES 

123                   6                                   126                      10

124                   8                                  136                       9

126                   10                                124                       8

236                    4

324                    7

223                    2

136                    9

etc.                    etc.

(Is it also possible to do one level deeper? Such as if I had

STORE   CODE    COUNT_OF_CODES and wanted the top 3 COUNT by each

STORE.)

---

[crawfe]

I picked this code to try and it worked. Thanks!