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;
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'
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'
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.