topic Re: proc tabulate and sorting formatted values in SAS Programming

proc tabulate and sorting formatted values

metallon — Wed, 25 Mar 2015 15:35:37 GMT

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;

Re: proc tabulate and sorting formatted values

RW9 — Wed, 25 Mar 2015 15:45:13 GMT

Hi,

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

Re: proc tabulate and sorting formatted values

ballardw — Wed, 25 Mar 2015 18:24:29 GMT

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

;

Re: proc tabulate and sorting formatted values

sbb — Wed, 25 Mar 2015 21:00:24 GMT

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.

Re: proc tabulate and sorting formatted values

metallon — Thu, 26 Mar 2015 08:14:06 GMT

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

Re: proc tabulate and sorting formatted values

metallon — Thu, 26 Mar 2015 10:37:17 GMT

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

;

Re: proc tabulate and sorting formatted values

data_null__ — Thu, 26 Mar 2015 10:53:39 GMT

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; 

Re: proc tabulate and sorting formatted values

Peter_C — Thu, 26 Mar 2015 13:31:06 GMT

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)