topic How to fix variable level position in proc tabulate in SAS Programming

How to fix variable level position in proc tabulate

lydiawawa — Tue, 23 Oct 2018 03:37:59 GMT

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!

Re: How to fix variable level position in proc tabulate

andreas_lds — Tue, 23 Oct 2018 07:22:32 GMT

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.

Re: How to fix variable level position in proc tabulate

s_lassen — Tue, 23 Oct 2018 10:25:44 GMT

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;

Re: How to fix variable level position in proc tabulate

FreelanceReinh — Tue, 23 Oct 2018 10:51:14 GMT

@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

a format for variable RACE which was defined using the NOTSORTED option of the VALUE statement (of PROC FORMAT)
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.

Re: How to fix variable level position in proc tabulate

Ksharp — Tue, 23 Oct 2018 13:24:47 GMT

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;

Re: How to fix variable level position in proc tabulate

lydiawawa — Wed, 24 Oct 2018 02:48:01 GMT

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?