## Proc Tabulate multi-dimensional table percentages

Solved
Occasional Contributor
Posts: 12

# Proc Tabulate multi-dimensional table percentages

I need help calculating percentages correctly. I'm not getting what I need from the proc statement below. Trying to get the variable2 and variable3 percentages separately into one table.

PROC TABULATE DATA=TEST;

CLASS VARIABLE1 VARIABLE 2 VARIABLE3 VARIABLE4;

TABLE VARIABLE1* (VARIABLE2 ALL VARIABLE3 ALL),VARIABLE4 *(N COLPCTN);

RUN;

Example of desired output:

VARIABLE4  VARIABLE4  VARIABLE4     VARIABLE4

N  %          N  %                    N   %               N  %

VARIABLE1   VARIABLE2

VARIABLE2

VARIABLE2

ALL

VARIABLE3

VARIABLE3

VARIABLE3

ALL

VARIABLE1 VARIABLE2

ALL

VARIABLE3

ALL ETC...

I'm getting % of total column instead of variable2 & variable3 seperately.   Thanks in advance.

Accepted Solutions
Solution
‎09-11-2011 12:48 AM
SAS Super FREQ
Posts: 9,365

## Re: Proc Tabulate multi-dimensional table percentages

Hi,

It looks to me like you want each "subgroup" of ethnicity or gender within VARIABLE1 to be 100%, since you show ALL for ethnicity and ALL for gender to both show 100%. COLPCTN by itself won't do what you want since it would use the entire column total as the denominator for division.

However TABULATE does provide a way to calculate a percentage based on the "subgroup" total. To make this happen, you would use a custom denominator definition, with the PCTN statistic.

Something like this (untested because not on my computer right now):

PROC TABULATE DATA=TEST;

CLASS VARIABLE1 VARIABLE 2 VARIABLE3 VARIABLE4;

TABLE VARIABLE1* (VARIABLE2 ALL VARIABLE3 ALL),

VARIABLE4 *(N PCTN<variable2 all variable3 all>);

RUN;

Basically, you instruct TABULATE to use the appropriate "subgroup" total as the denominator by putting the class variables inside the angle brackets for PCTN.

I don't know whether the forum posting will accurately let the < and > symbols appear, but if you look up custom denominator definition and TABULATE, you will find examples.

Cynthia

fixed GT and LT symbols in post

All Replies
Super Contributor
Posts: 394

## Proc Tabulate multi-dimensional table percentages

This forum is for discussions about "getting started" using the SAS Communities. Since your question is about a Base Reporting procedure you might get more responses if you post it in the ODS and Base Reporting forum. Good luck!

Super User
Posts: 10,761

## Proc Tabulate multi-dimensional table percentages

PROC TABULATE DATA=TEST;

CLASS VARIABLE1 VARIABLE 2 VARIABLE3 VARIABLE4;

TABLE VARIABLE1* (VARIABLE2 ALL VARIABLE3 ALL)  ALL   ,VARIABLE4 *(N COLPCTN);

RUN;

Ksharp

Occasional Contributor
Posts: 12

## Proc Tabulate multi-dimensional table percentages

Thanks, gave that a try but I got the same results as before. Will keep trying.

SAS Super FREQ
Posts: 9,365

## Proc Tabulate multi-dimensional table percentages

Hi,

I am having a hard time visualizing what you want from Proc tabulate.

For me, just seeing variable1 and variable2 repeated doesn't give me a good sense of what you want. Is there any way for you to share either a small subset of the real data and show the results you want or make a small dataset with values of AAA,BBB for variable1 and values of Z1, Z2, for variable2, etc?? The volume of data does not matter as much as getting a good picture of where you are starting from and the results you want or expect.

Cynthia

Occasional Contributor
Posts: 12

## Proc Tabulate multi-dimensional table percentages

Maybe this will make it clearer:

Example of desired output:

Fall2007     Fall 2008          Fall 2009         Fall 2010

N    %          N  %                    N   %               N  %

PROF    ethnicity                             5     33%

ethnicity                            5     33%

ethnicity                            5     34%

ALL                       15     100%

male                            5        25%

female                        10       75%

ALL                  15       100%

ASSOC PROF ethnicity               2         20%

ethnicity             5         50%

ethnicity             3       30%

ALL                  10   100%

male                   2       20%

female               8       80%

ALL                 10    100%

ASSIS PROF ethnicity

ethnicity

ethnicity

ALL

gmale

female

ALL

ETC...

Solution
‎09-11-2011 12:48 AM
SAS Super FREQ
Posts: 9,365

## Re: Proc Tabulate multi-dimensional table percentages

Hi,

It looks to me like you want each "subgroup" of ethnicity or gender within VARIABLE1 to be 100%, since you show ALL for ethnicity and ALL for gender to both show 100%. COLPCTN by itself won't do what you want since it would use the entire column total as the denominator for division.

However TABULATE does provide a way to calculate a percentage based on the "subgroup" total. To make this happen, you would use a custom denominator definition, with the PCTN statistic.

Something like this (untested because not on my computer right now):

PROC TABULATE DATA=TEST;

CLASS VARIABLE1 VARIABLE 2 VARIABLE3 VARIABLE4;

TABLE VARIABLE1* (VARIABLE2 ALL VARIABLE3 ALL),

VARIABLE4 *(N PCTN<variable2 all variable3 all>);

RUN;

Basically, you instruct TABULATE to use the appropriate "subgroup" total as the denominator by putting the class variables inside the angle brackets for PCTN.

I don't know whether the forum posting will accurately let the < and > symbols appear, but if you look up custom denominator definition and TABULATE, you will find examples.

Cynthia

fixed GT and LT symbols in post

Occasional Contributor
Posts: 12