Order proq freq bands in order of bands listed

[jeremy4]

Hi,

Can someone please help me add in code, so that the proc freq table lists the different bands I have created in order, rather than it being sorted in numerical order i.e. when I run my code, the '10,000 < NUMBER <= 20,000' results are before the row of results before '2,000 < NUMBER <= 4,000'. Would it be possible so that the proq freq lists bands in the order I want (i.e code that could be added to the proc freq statement)?:

Current code produces proc freq table that is sorted in numerical band order

PROC FREQ TABLE order from code written:

'0 <= NUMBER <= 2,000'

'10,000 < NUMBER <= 20,000'

'2,000 < NUMBER <= 4,000''

'4,000 < NUMBER <= 6,000'

'6,000 < NUMBER <= 8,000'

'8,000 < NUMBER <= 10,000'

proc freq order required (in the order that I have listed in my code)

'0 <= NUMBER <= 2,000'

'2,000 < NUMBER <= 4,000'

'4,000 < NUMBER <= 6,000'

'6,000 < NUMBER <= 8,000'

'8,000 < NUMBER <= 10,000'

'10,000 < NUMBER <= 20,000'

Code

data test;
   set ac.number_values;
   length BAND $25;

   if 0 <= NUMBER <= 2000 then PD_C_12_BAND = '0 <= NUMBER <= 2,000';
      else if 2000 < NUMBER <= 4000 then BAND = '2,000 < NUMBER <= 4,000'';
      else if 4000 < NUMBER <= 6000 then BAND = '4,000 < NUMBER <= 6,000';
      else if 6000 < NUMBER <= 8000 then BAND = '6,000 < NUMBER <= 8,000';

      else if 8000 < NUMBER <= 10000 then BAND = '8,000 < NUMBER <= 10,000';
      else BAND = '10,000 < NUMBER <= 20,000';
run;

proc freq data = test;

   tables BAND;

run;

[PeterClemmensen]

Like this?

proc format;
	value band 0      - 2000  = '0 <= NUMBER <= 2,000'
	           2000  <- 4000  = '2,000 < NUMBER <= 4,000'
			   4000  <- 6000  = '4,000 < NUMBER <= 6,000'
			   6000  <- 8000  = '6,000 < NUMBER <= 8,000'
			   8000  <- 10000 = '8,000 < NUMBER <= 10,000'
			   10000 <- 20000 = '10,000 < NUMBER <= 20,000'
	;
run;

data test;
	do _N_=1 to 1e5;
		x=rand('integer', 0, 20000);
		output;
	end;
run;

proc freq data=test;
   tables x;
   format x band.;
run;

[jeremy4]

Thanks for your reply, it's just that there are about 100,000 observations in my dataset and I want to use a proc freq to see the number of observations belonging to each band that I have created in my code. Is there another method, just so that the proc freq table lists the bands in the order that I have written in the code, rather than the numerical sorting of the bands from the proc freq results table that I have explained in my post? Thanks!

[PeterClemmensen]

The test data set that I posted contains 100.000 obs as well. Did you try to run it?

[ballardw]

---

@jeremy4 wrote:

Thanks for your reply, it's just that there are about 100,000 observations in my dataset and I want to use a proc freq to see the number of observations belonging to each band that I have created in my code. Is there another method, just so that the proc freq table lists the bands in the order that I have written in the code, rather than the numerical sorting of the bands from the proc freq results table that I have explained in my post? Thanks!

---

One of the other very strong advantages to the Proc Format approach is that the formats are applied at the time the Procedure such as Freq is run. So you can have multiple formats and use the one you want at a specific time without having to add multiple additional variables.

For example I work with age data and depending on the analysis or report requirement we group people by: 5 year age groups: like 20-24, 25-29, 30-34 etc.; 10 year age groups: 20-29, 30-39 etc.; specific age groups under/over age 50, or 45, or 65. With formats I only need one numeric age value and can use the specific format for the specific analysis. Note that the formats will group values for almost every analysis, reporting or graphing procedure SAS uses. And with a numeric value at heart the sort order never gets "Kafluey" because of the sort behavior of character values.

Plus for some specific procedures you can use a multilabel format that will show something like the 10000 to 20000 as well as the 10000-15000 and 15000-20000.

[Astounding · Accepted solution]

The format method works!

Another easy way would be to add a leading blank to most of the categories:

   length BAND $25;

           if 0   <= NUMBER <=  2000 then BAND = ' 0 <= NUMBER <= 2,000';
      else if 2000 < NUMBER <=  4000 then BAND = ' 2,000 < NUMBER <= 4,000'';
      else if 4000 < NUMBER <=  6000 then BAND = ' 4,000 < NUMBER <= 6,000';
      else if 6000 < NUMBER <=  8000 then BAND = ' 6,000 < NUMBER <= 8,000';
      else if 8000 < NUMBER <= 10000 then BAND = ' 8,000 < NUMBER <= 10,000';
      else BAND = '10,000 < NUMBER <= 20,000';

[Reeza]

When you recode the variables to text, it sorts alphabetically, but alphabetically isn't what you want. 

The way to get around that is to use a format to group the bands instead of recoding them manually. SAS will create the groups for you and order the correctly in this manner. This allows you to have the sort order as desired. 

So @PeterClemmensen solution of using a format is the correct answer here. 

Other alternatives include adding a number in front of your groups, adding another variable to the dataset to explicitly control the sort order. The easiest solution is the format option. 

---

@jeremy4 wrote:

Hi,

Can someone please help me add in code, so that the proc freq table lists the different bands I have created in order, rather than it being sorted in numerical order i.e. when I run my code, the '10,000 < NUMBER <= 20,000' results are before the row of results before '2,000 < NUMBER <= 4,000'. Would it be possible so that the proq freq lists bands in the order I want (i.e code that could be added to the proc freq statement)?:

Current code produces proc freq table that is sorted in numerical band order

PROC FREQ TABLE order from code written:

'0 <= NUMBER <= 2,000'

'10,000 < NUMBER <= 20,000'

'2,000 < NUMBER <= 4,000''

'4,000 < NUMBER <= 6,000'

'6,000 < NUMBER <= 8,000'

'8,000 < NUMBER <= 10,000'

proc freq order required (in the order that I have listed in my code)

'0 <= NUMBER <= 2,000'

'2,000 < NUMBER <= 4,000'

'4,000 < NUMBER <= 6,000'

'6,000 < NUMBER <= 8,000'

'8,000 < NUMBER <= 10,000'

'10,000 < NUMBER <= 20,000'

Code

data test;
   set ac.number_values;
   length BAND $25;

   if 0 <= NUMBER <= 2000 then PD_C_12_BAND = '0 <= NUMBER <= 2,000';
      else if 2000 < NUMBER <= 4000 then BAND = '2,000 < NUMBER <= 4,000'';
      else if 4000 < NUMBER <= 6000 then BAND = '4,000 < NUMBER <= 6,000';
      else if 6000 < NUMBER <= 8000 then BAND = '6,000 < NUMBER <= 8,000';

      else if 8000 < NUMBER <= 10000 then BAND = '8,000 < NUMBER <= 10,000';
      else BAND = '10,000 < NUMBER <= 20,000';
run;

proc freq data = test;

   tables BAND;

run;

---