How to fix variable level position in proc tabulate

[lydiawawa]

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!

[andreas_lds]

Afaik not possible within proc tabulate.

Just an idea:

• save result from proc tabulate in a dataset
• use a data-step and proc format to create a format
• use proc report on the dataset created by proc tabulate, clone the variable Race assign the format to the clone and use order + noprint

Unfortunately i don't have enough time for a closer look at the problem.

[s_lassen]

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]

Hi, @s_lassen, this will create sub-levels among the original levels? I will then need to feed temp into proc tabulate to produce the desired order?

[FreelanceReinh]

---

@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

1. a format for variable RACE which was defined using the NOTSORTED option of the VALUE statement (of PROC FORMAT)
2. the options PRELOADFMT and ORDER=DATA of the CLASS statement in PROC TABULATE.

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.

[Ksharp]

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;