proc tabulate and sorting formatted values

[metallon]

Hi SAS Experts,

My problem is that the tabulated table is not sorted how I would like to i.e. sorting by haupt_kat, unter_kat and zeitraum.

I searched for a while and came accross instructions to use the FORMAT in combination with PROC TABULATE.

But that did not do the trick either. haupt_kat and unter_kat are sorted BUT zeitraum isnt.

Any suggestions from your side are very welcome.

I have the following code:

proc format;

value $zeitraum

    0 = '7.11.2013'

    1 = '8.11.2013'

    2 = '30.6.2014'

    ;

RUN

;

proc tabulate data=daten.daten601 order=formatted;

class haupt_kat unter_kat zeitraum;

var normmw anz_nnnb anz_b sgü rwsgalt rwsgneu;

table haupt_kat='Hauptkategorie'*unter_kat='Unterkategorie'*zeitraum='Zeitraum',

    N='Anzahl' anz_nnnb='Anzahl NN/NB'*f=4.0;

Format zeitraum $zeitraum.;

run;

[RW9]

Hi,

Best to search for posts.  Here is one which may help:

[ballardw · Accepted solution]

Modify the format. By default in character values (the formatted) 3 comes before 7 and 8 even when part of "number" like 30. Putting a space before the 7 and 8 will have the 3 after blank.

OR since these look like date values it may have been better to create a date value to begin with then the order generally works as well as allowing change of format with sas supplied date formats to create groups base on weeks, months, quarters and other intervals with custom formats.

proc format;

value $zeitraum

    0 = ' 7.11.2013'

    1 = ' 8.11.2013'

    2 = '30.6.2014'

    ;

RUN

;

[metallon]

Hi ballardw,

I tried the following

proc format;

value $zeitraum

    0 = '  from 7.11.2013'

    1 = ' to 8.11.2013'

    2 = 'from 30.6.2014'

    ;

RUN

;

but that did not work. Sorry, I forgot that I have prefixed the dates with from and to so they are character varialbes. I thought

the sorting would happen by the values 0,1 or 2

[metallon]

got it

proc format;

value $zeitraum

     'bis 7.11.2013' = ' bis 7.11.2013'

     'vom 8.11.2013' = ' vom 8.11.2013'

     'vom 30.6.2014' = 'vom 30.6.2014'

    ;

RUN

;

[sbb]

Possibly these values for zeitraum are dates? If so, then maintain SAS numeric DATE variables, sort on that variable name, and apply a suitable SAS numeric FORMAT for the desired date-format.

[data_null__]

You want to use order=internal for that variable.  You can put the order option on the class statement and use more than one class statement. RTM

proc format;
   value $zeitraum
       0 = '7.11.2013'
       1 = '8.11.2013'
       2 = '30.6.2014'
       ;
   RUN;

data daten601;
   do zeitraum = '0','1','2';
      output;
      end;
   run;
proc tabulate;
   class zeitraum / order=internal;
   tables zeitraum;
   format zeitraum $zeitraum.;
   run;

[Peter_C]

Sounds like OP wants some class vars to have ORDER= FORMATTED and others INTERNAL.

We do this as CLASS  statement options.

Then two CLASS statements solve the problem much cleaner (and clearer) than special user formats

(imho)