turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- ODS and Base Reporting
- /
- Proc Tabulate multi-dimensional table percentages

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-01-2011 02:20 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-11-2011 12:48 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-02-2011 08:29 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-06-2011 04:46 AM

How about:

PROC TABULATE DATA=TEST;

CLASS VARIABLE1 VARIABLE 2 VARIABLE3 VARIABLE4;

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

RUN;

Ksharp

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-07-2011 12:52 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-08-2011 11:23 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-09-2011 05:29 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-11-2011 12:48 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-12-2011 02:53 PM

Thanks so much, that worked!