Desktop productivity for business analysts and programmers

Proc Format: preserve the order

Reply
Frequent Contributor
Posts: 76

Proc Format: preserve the order

Although I have used “(notsorted)” & ORDER=DATA PRELOADFMT to preserve the order of my user-defined format, why does Females still come before Males in my output table?
 
PROC FORMAT;
 
VALUE $SEXB (notsorted)
 
OTHER ='Males'
'F'='Females'
;
 
Run;
 
PROC TABULATE DATA=test F=COMMA9. FORMCHAR='           ' MISSING ;
CLASS ID AGE  SEX ETH/ ORDER=DATA PRELOADFMT MLF MISSING;
 
KEYLABEL                N=' '
                                SUM=' '
                                ALL ='Total';
TABLE (ALL ID=' '),(ALL AGE=' '),(ALL ETH=' ')*(ALL SEX=' ')/ PRINTMISS MISSTEXT='0';
FORMAT ID $ID. AGE AGED. ETH $ETH. SEX $SEXB.;
 
RUN;
 

Super User
Super User
Posts: 9,813

Re: Proc Format: preserve the order

BECAUSE F SORTS BEFORE M.

 

Please avoid coding all in uppercase you can see how it does not read as easy and sounds like shouting.  The character F has a lower ASCII code than M, therefore it appears before M.  For these types of scenarios, it is a good idea to have the underlying data as a numeric, and format that value to be the text you want, e.g. (and again note the code casing/indentation):

data have;
  set have;
  sexc=ifn(sex="F",2,1);
run;

proc format;
  value sex
    1="Females"
    2="Males";
run;

proc tabulate data=have ...;
  class id age sexc ...;
  ...
  format sexc sex.;
run;

You will see I create a coded version of the data, with a 1 or 2, then apply a format of the text to this numeric code.  

Super User
Posts: 6,921

Re: Proc Format: preserve the order

As long as you are willing to specify ORDER=DATA, try sorting your data set before running PROC TABULATE.  The sorted order could be BY DESCENDING SEX.

 

As you may have noticed, ORDER=DATA affects your other CLASS variables as well.

Super User
Posts: 10,848

Re: Proc Format: preserve the order

1) Try put order=data at PROC TABULATE

PROC TABULATE DATA=test ORDER=DATA ;
 
 
2) Put some white blanks before Males.
PROC FORMAT;
VALUE $SEXB 
OTHER ='  Males'
'F'='Females'
;
Run;
PROC TABULATE DATA=test ORDER=formatted ;
SAS Super FREQ
Posts: 830

Re: Proc Format: preserve the order

Have a look at the CLASSDATA= option on Proc TABULATE. It allows you to specify a datasets that will determine the ordering of the class variable values, see also sample below.

 

data classOrder;
  infile cards dlm="," truncover;
  input
   type : $8. origin : $6.
  ;
cards;
Hybrid,Europe
Hybrid,USA
Hybrid,Asia
Wagon,Europe
Truck,Europe
SUV,Europe
Sedan,Europe
Sports,Europe
;

proc tabulate
  data=sashelp.cars 
  classdata=classorder
  order=data
;
  class type origin;

  table type, origin ;
run;
Ask a Question
Discussion stats
  • 4 replies
  • 133 views
  • 0 likes
  • 5 in conversation