Proc Format: preserve the order

[apple]

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;

[RW9]

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.  

[Astounding]

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.

[Ksharp]

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 ;

[BrunoMueller]

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;