DATA Step, Macro, Functions and more

proc tabulate and sorting formatted values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

proc tabulate and sorting formatted values

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;


Accepted Solutions
Solution
‎03-25-2015 02:24 PM
Super User
Posts: 11,343

Re: proc tabulate and sorting formatted values

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

;

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: proc tabulate and sorting formatted values

Hi,

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

Solution
‎03-25-2015 02:24 PM
Super User
Posts: 11,343

Re: proc tabulate and sorting formatted values

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

;

Frequent Contributor
Posts: 133

Re: proc tabulate and sorting formatted values

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

Frequent Contributor
Posts: 133

Re: proc tabulate and sorting formatted values

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

;

Super Contributor
Super Contributor
Posts: 3,174

Re: proc tabulate and sorting formatted values

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.

Respected Advisor
Posts: 3,799

Re: proc tabulate and sorting formatted values

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;
Valued Guide
Posts: 2,177

Re: proc tabulate and sorting formatted values

Posted in reply to data_null__

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)

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 314 views
  • 6 likes
  • 6 in conversation