Customizing PROC FREQ outputs

[A_Perez]

I have produced a range of PROC FREQ tables, and I would like them ordered in a particular way. For example, in the screenshot attached, I would like them ordered as: 'Agree, Somewhat Agree, Neither Agree nor Disagree, Somewhat Disagree, Disagree'.

I know that I am able to order the observations based on alphabetical order, or frequency etc. but is there a way to order them completely customized? 

One thing I am thinking about doing is to rank the values : 

proc format;
value $rank
'Agree' = 1
'Somewhat Agree' = 2
'Neither Agree nor Disagree' = 3; etc. etc. 
run;

Then sort by Rank, and THEN do the proc freq and order by data (the order they appear)?

But hoping there is a quicker workaround than doing that.

Appreciate the guidance. 

[ballardw]

Personally what I do with such data is to read the original data into a numeric value in the order I want. Then use another format to display the text as desired. By default Proc Freq will use the numeric value of the variable for display order. Other procedures you may need to use another option to control order.

One of the big advantages of this approach is a simple different format can create different analysis grouped values which are honored by analysis, reporting and graphing procedures generally.

Here is an example of creating a custom  informat (the invalue statement), using it to read a couple of variables and creating output with proc freq with two different display formats.

proc format;
invalue scale (upcase)
'AGREE'                      = 1
'SOMEWHAT AGREE'             = 2
'NEITHER AGREE NOR DISAGREE' = 3
'SOMEWHAT DISAGREE'          = 4
'DISAGREE'                   = 5
other                        = .
;
value scale
1 = 'Agree'                     
2 = 'Somewhat agree'            
3 = 'Neither agree nor disagree'
4 = 'Somewhat disagree'         
5 = 'Disagree' 
; 
value scale_3level
1,2 = 'Agree/somewhat agree'                     
3   = 'Neither agree nor disagree'
4,5 = 'Somewhat disagree/disagree'         
; 

data example;
   infile datalines dlm=',';
   informat x y scale.;
   input x y;
datalines;
Agree,Agree
Somewhat agree,Disagree
Somewhat disagree,Somewhat disagree
Neither agree nor disagree,agree
Disagree,Disagree
Somewhat agree,Somewhat agree
;

proc freq data=example;
   table x y;
   format x y scale.;
run;

proc freq data=example;
   table x y;
   format x y  scale_3level.;
run;

If you don't want to reread your base data you could add a numeric version of your existing variable(s) with an input function call in a data step;

data want;
   set have;
   numvar = input(var, scale.);
run;

and use the format desired.

If you have many similar variables then use arrays to do the same to all of them. The more variables involved the more likely I would be to reread the raw data file.

[Reeza]

This comes down to good data structures. Recode your variable so it's numeric in the order you want and then display the labels via the format. FREQ will use the underlying values for the order so it'll show cleanly.

You can create an informat to convert to numeric and then apply a format for the display.

Untested.


proc format;
invalue likert
'Agree' = 1
'Somewhat Agree' = 2
...;

value likert
1 = 'Agree'
2 = 'Somewhat Agree'
.....;
run;

data want;
set have;

num_var = input(old_var, likert.);
fomat num_var likert.;
run;

proc freq data=want;
table num_var;
format num_var likert.; *not really needed;
run;