topic Proc Format: preserve the order in SAS Enterprise Guide

Proc Format: preserve the order

apple — Wed, 17 Jan 2018 11:22:16 GMT

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;

Re: Proc Format: preserve the order

RW9 — Wed, 17 Jan 2018 11:35:26 GMT

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.

Re: Proc Format: preserve the order

Astounding — Wed, 17 Jan 2018 11:40:14 GMT

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.

Re: Proc Format: preserve the order

Ksharp — Wed, 17 Jan 2018 12:18:57 GMT

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 ;

Re: Proc Format: preserve the order

BrunoMueller — Wed, 17 Jan 2018 12:35:40 GMT

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;