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.)
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.)
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
Here is the link https://documentation.sas.com/?docsetId=proc&docsetTarget=n1hxon9vm350ikn19oeualfap8qy.htm&docsetVer...
Thanks. I will study that.
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;
proc freq data=have order=freq;
tables code / maxlevels=3
nopct nocum;
weight count_of_codes;
run;
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).
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"
Thanks. I will try it.
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.)
I picked this code to try and it worked. Thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.