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

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

mduarte — Mon, 15 Feb 2016 06:13:35 GMT

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

Any help appreciated. Thanks.

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

Reeza — Mon, 15 Feb 2016 06:31:40 GMT

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;

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

Ksharp — Mon, 15 Feb 2016 08:09:20 GMT

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;

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

data_null__ — Mon, 15 Feb 2016 12:27:27 GMT

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;