PROC FREQ - how to sort second level variables in a two-way table?

[lillianlui]

Hi everyone, I am new to SAS and am learning the programming courses online.

I just learned to use order=freq to sort the first level variables in descending order by frequency. I wonder if it's possible to do the same for the second level variables?

Please see screenshot below. The report is now sorted by Type - National Historic Site (total 76),  National Monument (total 69). How do I sort the Region within National Historic Site so that it shows - Northeast (26), Midwest (16).. and so on?

Here are the original codes. I tried to add a BY statement and more arguments after order=. Didn't work. 

Does anyone know the answer?

I would also love to have any beginner tips to search/use the SAS documentation!

Thank you so much

Lillian

[ed_sas_member]

Hi @lillianlui 

I am not sure there is a simple answer to your question.

ORDER=FREQ works well for a one-way frequency table but it seems tricky to get similar results with a two-way frequency table.

Here is an approach to get the desired results:

1/ Output the results of PROC FREQ in a dataset (here it is work.np_codelookup)

2/ Sort the results according to the type and the count

3/ Create the report based on this output.

/*** Run a proc freq and output the results in a dataset: work.np_codelookup */

	proc freq data=pg1.np_codelookup order=freq noprint;
		tables type*region / outpct out=work.np_codelookup (drop=percent pct_col);
		where type in ('National Historic Site', 'National Monument','National Park');
	run;

/*** Sort the results by type and descending counts */

	proc sort data=work.np_codelookup;
		by type descending count;
	run;

/*** Create the report */
	/* NB: TIP:
		- the 'group' category in the define statement for type and region allows you to suppress repetitious printing of the values
		- the break after statement allows you to compute the "ALL" row for each type
		- creating the new 'region_dummy' variable allow you to add the 'ALL' in the region field */

	proc report data=work.np_codelookup_weight;
		column type region region_dummy count pct_row;
		define type			/ group;
		define region		/ group noprint;
		define region_dummy / computed 'Region';
		define count		/ analysis;
		define pct_row 		/ analysis f=8.2;
		break after type	/ summarize suppress;
		
		compute region_dummy / char length=20;
			if _break_='Type' then region_dummy="Total";
			else region_dummy = region;
		endcomp;
	run;

Best,

[lillianlui]

Hi @ed_sas_member , thank you! Your codes look fancy haha. I tried to run it but the result is still not what I want. Same problem with @ChrisNZ's solution: output data is all in the desired order but the report is not. Thoughts?

[ChrisNZ · Accepted solution]

This then:

proc freq data=SASHELP.CARS order=freq noprint;
  table MAKE* TYPE /outpct out=SUM ;
run;

data SUM2;
  set SUM ;
  by MAKE notsorted ;
  MAKE_NO+first.MAKE;
run;

proc sort out=SUM3; 
  by MAKE_NO descending COUNT TYPE ;
run;
      
proc report nowd;
	column MAKE TYPE COUNT PCT_ROW;
	define MAKE			/ group order=data;
	define TYPE	  	/ display ;
	define COUNT		/ analysis;
	define PCT_ROW 		/ analysis f=8.2;
	break after MAKE	/ summarize suppress;
	compute PCT_ROW ;
		if TYPE=' ' then TYPE="Total";
	endcomp;
run;

Make	Type	Frequency Count	Percent of Row Frequency
Toyota	Sedan	16	57.14
	SUV	5	17.86
	Truck	3	10.71
	Sports	2	7.14
	Hybrid	1	3.57
	Wagon	1	3.57
	Total	28	100.00
Chevrolet	Sedan	15	55.56
	Truck	5	18.52
	SUV	4	14.81
	Sports	2	7.41
	Wagon	1	3.70
	Total	27	100.00
Mercedes-Benz	Sedan	16	61.54
	Sports	5	19.23
	Wagon	3	11.54
	SUV	2	7.69
	Total	26	100.00
Ford	Sedan	11	47.83
	SUV	4	17.39
	Sports	3	13.04
	Truck	3	13.04
	Wagon	2	8.70
	Total	23	100.00

[lillianlui]

it works!! thank you so much🙏🙏

[ChrisNZ]

Another way:

proc freq data=SASHELP.CARS order=freq noprint;
  table MAKE* TYPE / out=SUM ;
run;

data SUM2;
  set SUM ;
  by MAKE notsorted ;
  MAKE_NO+first.MAKE;
run;

proc sort out=SUM3; 
  by MAKE_NO descending COUNT TYPE ;
run;

proc freq data=SUM3 order=data;
  table MAKE* TYPE/crosslist nocum nopercent nocol norow ;
  weight COUNT;
run;

[lillianlui]

Hi Chris, thanks for your answer. It works all fine (including the output data) until the final step of proc freq. Looks like the Type order is now following the first Make category:

Tried the same method on my original dataset, same problem:

Not sure if there's a way to go around this? 

I've learned a lot regardless so thank you 🙂

[ChrisNZ]

You are right. Apologies.

I somehow recalled that PROC FREQ did not do that, and this blinded me when I checked the results before posting. 

Thankfully you have @ed_sas_member 's proc report! 🙂