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.
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;
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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.