DATA Step, Macro, Functions and more

Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

Hi - Just wondering how I can use the order in PROC FORMAT to sort output in a table.

 

Sample code:

proc format;
   value mpg_band
      	LOW - <16 = '<=15'
      	16 - <18 = '16-17'
	  	18 - <25 = '18-24'
		25 - <30 = '25-29'
		30 - <35 = '30-34'
		35 - <40 = '35-39'
		40 - <45 = '40-44'
		45 - <50 = '45-49'
		50 - <55 = '50-54'
		55 - <60 = '55-59'
		60 - <65 = '60-64'
		65 - <70 = '65-69'
		70 - <75 = '70-74'
		75 - HIGH = '75+';
run;
data new_data;
	set SASHELP.cars;
	mpg_band = put(mpg_highway,mpg_band.);
run;

proc sort data=new_data;
by origin mpg_band;
run;
proc freq data=new_data;
	tables origin*mpg_band /out=freq_data;
	by origin NOTSORTED;
run;

 

As shown in an extract of the output, the <= 15 is the last column, however I would like it to be the first column ... 

Capture.PNG

 

Any help appreciated.  Thanks.  


Accepted Solutions
Solution
‎02-15-2016 11:55 PM
Super User
Posts: 19,768

Re: Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

Don't convert the variable, use the original variable and apply the format. SAS will sort it correctly for you.

There's two sets of code below, hopefully it illustrates the issue.

 

proc format;
   value mpg_band
      	LOW - <16 = '<=15'
      	16 - <18 = '16-17'
	  	18 - <25 = '18-24'
		25 - <30 = '25-29'
		30 - <35 = '30-34'
		35 - <40 = '35-39'
		40 - <45 = '40-44'
		45 - <50 = '45-49'
		50 - <55 = '50-54'
		55 - <60 = '55-59'
		60 - <65 = '60-64'
		65 - <70 = '65-69'
		70 - <75 = '70-74'
		75 - HIGH = '75+';
run;
data new_data;
	set SASHELP.cars;
	mpg_band = put(mpg_highway,mpg_band.);
run;

proc sort data=new_data;
by origin mpg_highway;
run;

proc freq data=new_data;
by origin;
	tables origin*mpg_highway /out=freq_data;
        format mpg_highway mpg_band.;
run;

OR

proc freq data=sashelp.cars;
tables origin*mpg_highway/out=freq_data2;
format mpg_highway mpg_band.;
run;

View solution in original post


All Replies
Solution
‎02-15-2016 11:55 PM
Super User
Posts: 19,768

Re: Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

Don't convert the variable, use the original variable and apply the format. SAS will sort it correctly for you.

There's two sets of code below, hopefully it illustrates the issue.

 

proc format;
   value mpg_band
      	LOW - <16 = '<=15'
      	16 - <18 = '16-17'
	  	18 - <25 = '18-24'
		25 - <30 = '25-29'
		30 - <35 = '30-34'
		35 - <40 = '35-39'
		40 - <45 = '40-44'
		45 - <50 = '45-49'
		50 - <55 = '50-54'
		55 - <60 = '55-59'
		60 - <65 = '60-64'
		65 - <70 = '65-69'
		70 - <75 = '70-74'
		75 - HIGH = '75+';
run;
data new_data;
	set SASHELP.cars;
	mpg_band = put(mpg_highway,mpg_band.);
run;

proc sort data=new_data;
by origin mpg_highway;
run;

proc freq data=new_data;
by origin;
	tables origin*mpg_highway /out=freq_data;
        format mpg_highway mpg_band.;
run;

OR

proc freq data=sashelp.cars;
tables origin*mpg_highway/out=freq_data2;
format mpg_highway mpg_band.;
run;
Super User
Posts: 10,018

Re: Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

Since you already transform it into character value by PUT() . Try Add a blank before '<=15' .

 

proc format;
value mpg_band
LOW - <16 = '  <=15'
16 - <18 = '16-17'
18 - <25 = '18-24'
25 - <30 = '25-29'
30 - <35 = '30-34'
35 - <40 = '35-39'
40 - <45 = '40-44'
45 - <50 = '45-49'
50 - <55 = '50-54'
55 - <60 = '55-59'
60 - <65 = '60-64'
65 - <70 = '65-69'
70 - <75 = '70-74'
75 - HIGH = '75+';
run;

Respected Advisor
Posts: 3,799

Re: Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

You don't need to a new variable you just need the proper options.

 

proc format;
   value mpg_band
      LOW - <16   = '<=15'
      16 - <18    = '16-17'
      18 - <25    = '18-24'
      25 - <30    = '25-29'
      30 - <35    = '30-34'
      35 - <40    = '35-39'
      40 - <45    = '40-44'
      45 - <50    = '45-49'
      50 - <55    = '50-54'
      55 - <60    = '55-59'
      60 - <65    = '60-64'
      65 - <70    = '65-69'
      70 - <75    = '70-74'
      75 - HIGH   = '75+'
      ;
   run;


proc sort data=sashelp.cars out=new_data;
   by origin;
   run;
proc freq data=new_data order=internal;
	tables origin*mpg_highway /out=freq_data;
	by origin;
   format MPG_Highway mpg_band.;
   run;

Capture.PNG

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 228 views
  • 0 likes
  • 4 in conversation