Hi All,
Suppose I want to tabulate a table with counts of races. Each level under variable Race has different frequencies such as:
Race N
White 1000
AA 900
Asian 400
Hispanic 700
Blank 500
Other 450
I set order = data for class variable Race, which sorts N from most count to least. However, I want to fix Blank and Other to always be the bottom 2. How should I code for such arrangement?
Thank you!
Afaik not possible within proc tabulate.
Just an idea:
Unfortunately i don't have enough time for a closer look at the problem.
You can do it like this, then ORDER=DATA should work:
data temp;
set
have(where=(race not in('Other','Blank'))
have(where=(race='Blank'))
have(where=(race='Other'))
;
run;
@lydiawawa wrote:I set order = data for class variable Race, which sorts N from most count to least.
Hi @lydiawawa,
I don't think your statement is true: order = freq would sort by descending count, whereas order = data sorts values according to their order in the input data set -- unless the additional option PRELOADFMT is specified, and this is the key to your problem.
You need
The CLASS levels will then be displayed in the order that was used in the format definition (VALUE statement).
Example:
/* Create a format with the NOTSORTED option */
proc format;
value race (notsorted)
4='White'
1='AA'
2='Asian'
3='Hispanic'
.='Blank'
other='Other';
run;
/* Create test data for demonstration */
data test(drop=n);
input race n;
do _n_=1 to n;
output;
end;
format race race.;
label race='Race';
cards;
1 900
2 400
3 700
4 1000
5 200
6 250
. 500
run;
/* Use options PRELOADFMT and ORDER=DATA to obtain the desired sort order */
proc tabulate data=test;
class race / missing preloadfmt order=data;
table race, n*f=6.;
run;
The same works with a character format or a format that is only temporarily applied in the PROC TABULATE step using a FORMAT statement.
Did you try CLASSDATA= ?
data race;
input race;
cards;
6
5
4
1
2
3
.
;
run;
data test(drop=n);
input race n;
do _n_=1 to n;
output;
end;
label race='Race';
cards;
1 900
2 400
3 700
4 1000
5 200
6 250
. 500
run;
proc tabulate data=test classdata=race;
class race / missing order=data ;
table race, n*f=6.;
run;
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.