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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.