CODEWITHSUNDEEP
sastabulate
D. Studer
D. Studer

Reputation: 1875

SAS: Change order of variable values in PROC TABULATE

I have the following sample data:

DATA offenders;
INPUT id :$12. age :3. sex :$1. residenceStatus :$1.;
INFORMAT id $12.;
INFILE DATALINES DSD;
DATALINES;
1,30,m,A
2,20,f,B
3,16,u,X
4,55,m,X
5,60,f,Y
6,20,f,A
7,34,m,B
8,63,m,X
9,23,m,X
10,19,f,A
11,56,f,A
12,25,u,X
13,31,u,A
14,24,f,B
15,27,m,A
16,20,m,B
17,21,u,A
18,43,f,A
19,44,u,A
20,41,m,B
;
RUN;

proc format;
 value agegrp 1-20 = '1-20'
              21-30 = '21-30'
              31-40 = '31-40'
              41-50 = '41-50'
              51-60 = '51-60'
              61-70 = '61-70';
 value $status 'A' = 'Status A'
                'B' = 'Status B'
                'X' = 'Status X'
                'Y' = 'Status Y';
run;

Now, I am creating a crosstable using PROC TABULATE:

proc tabulate;
class age sex residenceStatus;
table sex="", (ALL residenceStatus="") * age="" /misstext="0";
format age agegrp. residenceStatus $status.;
run;

But how can I change the order of a specific variable within the crosstable?

For example, in the example above the columns are:

   ALL            Status A       Status B       Status X       Status Y
   1-20 21-30 ... 1-20 21-30 ... 1-20 21-30 ... 1-20 21-30 ... 1-20 21-30 ...
f
m
u

What if I wanted ALL Status X Status B Status A Status Y instead? Thanks for any help!

Upvotes: 0

Views: 1721

Answers (1)

Richard
Richard

Reputation: 27508

The option PRELOADFMT is used to force a specific ordering of values of a CLASS variable.

Change the definition of the custom format $status to specify the (NOTSORTED) option and list the mappings in the desired output order. NOTSORTED tells the procedure to NOT sort by the start values -- this affects the internals of the format and how it interacts with other procedures.

Then in the TABULATE, for the CLASS variable needing the specific order as defined, specify the options ORDER=DATA PRELOADFMT. NOTE: The default ordering is formatted value ascending. 

proc format;
 value $status (notsorted)
  'X' = 'Status X'
  'B' = 'Status B'
  'A' = 'Status A'
  'Y' = 'Status Y'
 ;
run;

proc tabulate;

  class age sex;                                 /* <---- default ordering */
  class residenceStatus / preloadfmt order=data; /* <---- custom ordering  */

  table sex="", (ALL residenceStatus="") * age="" /misstext="0";
  format 
    age agegrp. 
    residenceStatus $status.                     /* <---- defined with (NOTSORTED) */
  ;     
run;

Upvotes: 2

Related Questions