BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mduarte
Quartz | Level 8

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.  

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Reeza
Super User

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;
Ksharp
Super User

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;

data_null__
Jade | Level 19

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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