topic Re: Creating a table of highest n values in SAS Programming

Creating a table of highest n values

crawfe — Mon, 09 Mar 2020 14:49:20 GMT

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

Re: Creating a table of highest n values

novinosrin — Mon, 09 Mar 2020 14:53:13 GMT

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&docsetVersion=9.4&locale=en

Re: Creating a table of highest n values

Patrick — Mon, 09 Mar 2020 15:16:27 GMT

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;

Re: Creating a table of highest n values

Watts — Mon, 09 Mar 2020 15:46:41 GMT

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

Re: Creating a table of highest n values

Rick_SAS — Mon, 09 Mar 2020 15:50:27 GMT

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"

Re: Creating a table of highest n values

Reeza — Mon, 09 Mar 2020 15:50:59 GMT

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

Re: Creating a table of highest n values

crawfe — Mon, 09 Mar 2020 17:39:17 GMT

Thanks. I will study that.

Re: Creating a table of highest n values

crawfe — Mon, 09 Mar 2020 17:41:18 GMT

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

Re: Creating a table of highest n values

crawfe — Mon, 09 Mar 2020 17:42:40 GMT

Thanks. I will try it.

Re: Creating a table of highest n values

crawfe — Mon, 09 Mar 2020 17:43:24 GMT

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