topic Re: Find top categories of a two-way classification table in PROC FREQ in SAS Studio

Find top categories of a two-way classification table in PROC FREQ

SIgnificatif — Wed, 26 Dec 2018 11:56:26 GMT

Hi all,
This question that is related to the SAS learning ( Programming 1 > Lesson 5 > Creating Frequency Reports > Level 2 Practice: Creating Two-Way Frequency Reports :

title1 'Selected Park Types by Region';
ods graphics on;
proc freq data=pg1.np_codelookup order=freq;
    tables Type*Region /  nocol crosslist 
           plots=freqplot(groupby=row scale=grouppercent orient=horizontal);
    where Type in ('National Historic Site', 'National Monument', 'National Park');
run;
title;

How to select the top 3 from Type instead of writing this ?

where Type in ('National Historic Site', 'National Monument', 'National Park');

Thank you for the response./ Update : Merry Xmas, Problem solved by the great community, thanks to everyone!

Re: Find top categories of a two-way classification table in PROC FREQ

Rick_SAS — Sun, 23 Dec 2018 12:05:41 GMT

In a two-way table, it is not entirely clear what you mean by "top 3." Do you mean that among all the cells, you want to print the levels of the cells that have the largest counts? If so, sort the CROSSLIST table in descending order and print or subset the first 3 observations:

proc freq data=sashelp.cars order=freq noprint;
    tables Type*Origin /  nocol crosslist out=FreqOut;
run;

proc sort data=FreqOut;
by descending Count;
run;

proc print data=FreqOut(obs=3);
run;

Re: Find top categories of a two-way classification table in PROC FREQ

mkeintz — Sun, 23 Dec 2018 13:31:06 GMT

I think you want a two-way frequency containing all the cells for the 3 most populous rows (variable TYPE).

proc freq data=np_codelookup order=freq  noprint;
  tables type*region /out=TR_freqs;
run;

data freqsubset;
  set tr_freqs;
  if type^=lag(type) then ntypes+1;
  if ntypes>3 then stop;
run;

ods graphics on;
proc freq data=freqsubset order=freq;
  tables Type*Region /  nocol crosslist 
         plots=freqplot(groupby=row scale=grouppercent orient=horizontal);
  weight count;
run;
ods graphics off;

Apparently the "order=freq" options orders results by descending row frequency (luckily not by descending cell freq). So data set TR_FREQS is ordered as wanted, with one observation per cell and a new variable COUNT. Then data set freqsubset is created just to include the first 3 types. Using this data set in the 2nd proc freq with "weight count" produces what I think you want.