topic Proc Tabulate multi-dimensional table percentages in ODS and Base Reporting

Proc Tabulate multi-dimensional table percentages

Patty — Thu, 01 Sep 2011 18:20:25 GMT

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

ALL

VARIABLE3

ALL

VARIABLE1 VARIABLE2

ALL

VARIABLE3

ALL ETC...

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

Proc Tabulate multi-dimensional table percentages

Tim_SAS — Fri, 02 Sep 2011 12:29:13 GMT

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!

Proc Tabulate multi-dimensional table percentages

Ksharp — Tue, 06 Sep 2011 08:46:33 GMT

How about:

PROC TABULATE DATA=TEST;

CLASS VARIABLE1 VARIABLE 2 VARIABLE3 VARIABLE4;

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

RUN;

Ksharp

Proc Tabulate multi-dimensional table percentages

Patty — Wed, 07 Sep 2011 16:52:28 GMT

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

Proc Tabulate multi-dimensional table percentages

Cynthia_sas — Thu, 08 Sep 2011 15:23:01 GMT

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

Proc Tabulate multi-dimensional table percentages

Patty — Fri, 09 Sep 2011 21:29:45 GMT

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

ALL

gmale

female

ALL

ETC...

Re: Proc Tabulate multi-dimensional table percentages

Cynthia_sas — Sun, 11 Sep 2011 04:48:15 GMT

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

Proc Tabulate multi-dimensional table percentages

Patty — Mon, 12 Sep 2011 18:53:02 GMT

Thanks so much, that worked!