proc tabulate order options-data ,internal,formatted,freq

[Ronein]

Hello

I want to run multiple proc tabulated.

I dont understand why outcome order of order=internal is

I dont understand why outcome order of order=Formatted is

May anyone explain please?

 data have;
 input CustID  gk  wealth;
 cards;
 1 8 100
 2 6 200
 3 11 300
 4 2 400
 5 4 500
 6 4 600
 7 2 700
 8 11 800
 9 8 900
 10 0 1000
 11 9 100
 12 9 200
 13 10 300
 ;
 Run;
proc format;
value gk_fmt
9-10='9--10'
0='0'
2-7='2--6'
8='8'
11='11';
run;
/****Order by the value appear in raw data***/
proc tabulate data=have;
class gk/order=data;
format gk gk_fmt.;
var wealth   ;              ;
table gk=' ' All='Total',
      n='Nr'*f=comma23. 
      pctn='PCT'*f=8.1
      wealth=' '*(sum='Sum_wealth'*f=comma23.1 pctsum='PCT_wealth'*f=8.1)/box='Gk'
   ;
run;
/****Order by freq***/
proc tabulate data=have;
class gk/order=freq;
format gk gk_fmt.;
var wealth   ;              
table gk=' ' All='Total',
      n='Nr'*f=comma23. 
      pctn='PCT'*f=8.1
      wealth=' '*(sum='Sum_wealth'*f=comma23.1 pctsum='PCT_wealth'*f=8.1)/box='Gk'
   ;
run;


proc tabulate data=have;
class gk/order=formatted;
format gk gk_fmt.;
var wealth   ;              
table gk=' ' All='Total',
      n='Nr'*f=comma23. 
      pctn='PCT'*f=8.1
      wealth=' '*(sum='Sum_wealth'*f=comma23.1 pctsum='PCT_wealth'*f=8.1)/box='Gk'
   ;
run;


proc tabulate data=have;
class gk/order=internal;
format gk gk_fmt.;
var wealth   ;              
table gk=' ' All='Total',
      n='Nr'*f=comma23. 
      pctn='PCT'*f=8.1
      wealth=' '*(sum='Sum_wealth'*f=comma23.1 pctsum='PCT_wealth'*f=8.1)/box='Gk'
   ;
run;

[PaigeMiller · Accepted solution]

When you have a numeric variable, and assign a format to it, the underlying value of the variable in the table is still a number. Your PROC FORMAT uses

proc format;
value gk_fmt
9-10='9--10'
0='0'
2-7='2--6'
8='8'
11='11';
run;

Anything that gets a formatted value of '0' will have the underlying numeric value of 0. Anything that has value 2-7 will appear as (that's what formats do, they change the appearance) '2--6' but the underlying value is probably 2 (depends on the data). So sorting by the internal (underlying) value means the values will be sorted numerically, 0 comes first, 2 comes next, and so on.

If you choose order=Formatted, then the formatted values are sorted, formatted values are text not numeric, and they will sort alphabetically, '0' first, then '11' next, because alphabetically 0 is followed by the first 1 in the formatted value '11'

[ballardw]

Since your variable Gk is numeric then "internal" means use the numeric order of the variable.

Formatted: the value of the format. Which result is CHARACTER, so uses the character sort order. When you have character values the order starts with the first character. Since the formatted value of 11 is the only formatted value that starts with 1 then it is the value that comes after 0. Then the value that starts with 2. IF you had more values then the alphabetical order of the second characters would determine the order of the values that started with the same character, then the 3rd and so on. 

You may also learn something with this example:

proc format;
value gk_fmt
9-10='9--10'
0='0'
2-7='2--6'
8='8'
11='11';
value gk_fmt_b 
9-10=' 9--10'
0   =' 0'
2-7 =' 2--6'
8   =' 8'
11  ='11';
run;


proc tabulate data=have;
title "Order by format gk_fmt_b";
class gk/order=formatted;
format gk gk_fmt_b.;
var wealth   ;              
table gk=' ' All='Total',
      n='Nr'*f=comma23. 
      pctn='PCT'*f=8.1
      wealth=' '*(sum='Sum_wealth'*f=comma23.1 pctsum='PCT_wealth'*f=8.1)/box='Gk'
   ;
run;title;

The format Gk_fmt_b is define with spaces for some of those values. So the SPACE is the first character compared for determining or order. The basic behavior of ODS output will not display that leading space however.

Since Total is coming from the ALL option it should not be considered at all for the order of the variable Gk.