Turn on suggestions

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

Showing results for

- Home
- /
- Programming
- /
- ODS Reports
- /
- Proc Tabulate multi-dimensional table percentages

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 09-01-2011 02:20 PM
(3279 views)

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.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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...

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks so much, that worked!

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.