proc tabulate-control order rows and columns

[Ronein]

Hello

In my data set I have two numeric categorical variables .

I want to create two way table that calculate freqs.

I want to display the categories in the char values (For var Class values-F,C,Y  and for var gender values-Male,Female)

I want to control the order of rows and columns in following order-For var Class -C,F,Y and for var genfer-Male,Female

What is the way to do it via proc tabulate?

proc format;
value classcode_fmt
8='F'
9='C'
11='Y'
;
Run;
proc format;
value classcode_order_fmt
9=1
8=2
11=3
;
Run;
proc format;
INVALUE classcode_order_ordinal
'C'=1
'F'=2
'Y'=3
;
Run;


proc format;
value gender_fmt
1='Female'
2='Male'
;
Run;
proc format;
value gender_order_fmt
2=1
1=2
;
Run;
proc format;
INVALUE gender_order_ordinal
'Male'=1
'Female'=2
;
Run;


data have;
input class gender;
cards;
8 1
9 1
11 1
11 2
11 2
11 1
9 2
8 1
;
Run;


proc tabulate data=have format=8.2 MISSING;
class class  gender;
table class='' all, (gender all)*(n=''*f=8.) / rts=10  misstext='0';
run;

 

[ballardw · Accepted solution]

If you aren't going to use the formats in YOUR code why include them at all? It just clutters up the post.

You have no CHAR values. Both of the variables in your data set are numeric. So please do not think of them as "char". You want to display characters

Since a single variable in Proc Tabulate is only going to use one format and no informats I don't see why you included all that format stuff.

This example:

proc tabulate data=have format=8.2 MISSING;
class class /order=formatted ;
class gender /descending;
format class classcode_fmt. gender  gender_fmt.;
table class='' all, 
     (gender all)*(n=''*f=8.) 
     / rts=10  misstext='0'
;
run;

Produces the stated orders. But that may be just an accident that you tried to use two orders that would have a sort order solution.

Format related options can be used to affect the order of appearance in Proc tabulate.

Change the order of the VALUES in the proc format with the NOTSORTED option on the values statement and then use PRELOADFMT with the Order=data option. When you use NOTSORTED the order of the values stored by proc format are  not changed (normally there is an internal order ). The options in Proc Tabulate with Preloadfmt then use the order of definition in the format.

Note again, this is to have Class results appear in  F Y C order (which not going to be any type of typical sort result)

proc format;
value Alt_classcode_fmt (notsorted)
8='F'
11='Y'
9='C'
;
Run;


proc tabulate data=have format=8.2 MISSING;
class class /preloadfmt order=data;
class gender /descending;
format class Alt_classcode_fmt. gender  gender_fmt.;
table class='' all, 
     (gender all)*(n=''*f=8.) 
     / rts=10  misstext='0' 
;
run;

You can also add variables to the data to use instead of your class variables that will sort with Order=Data or Order=Unformatted and then provide a new format with the values to appear in that order.

Please do pay attention to the interaction between the Format definition as other options, especially MULTILABEL have a lot of interaction with Order, Preloadfmt and PRINTMISS options of Proc Tabulate. The warning about Multilabel is pretty important to be aware of as Tabulate is one of the few procedures that will use the multiple labels and the order the format is created has a LOT of impact on the result.

[PaigeMiller]

In the CLASS statement, there is a DESCENDING option and also an ORDER= option.

[Ronein]

data have;
input class gender;
cards;
8 1
9 1
11 1
11 2
11 2
11 1
9 2
8 1
;
Run;

/***Bad order of columns and rows************/
/***Bad order of columns and rows************/
/***Bad order of columns and rows************/
proc format;
value F1mt
9='C'
11='Y'
8='F'
;
Run;
proc format;
value F2mt
2='Male'
1='Female'
;
Run;
proc tabulate data=have format=8.2 MISSING;
class class  gender;
table class='' all, (gender all)*(n=''*f=8.) / rts=10  misstext='0';
format class F1mt. gender  F2mt.;
run;

/*****Solution to control order ot rows and columns***********/
/*****Solution to control order ot rows and columns***********/
/*****Solution to control order ot rows and columns***********/
proc format;
value Alt_classcode_fmt (notsorted)
9='C'
8='F'
11='Y'
;
Run;
proc format;
value gender_fmt  (notsorted)
2='Male'
1='Female'
;
Run;
proc tabulate data=have format=8.2 MISSING;
class class /preloadfmt order=data;
class gender /descending;
format class Alt_classcode_fmt. gender  gender_fmt.;
table class='' all, (gender all)*(n=''*f=8.) / rts=10  misstext='0' 
;
run;