The SAS Output Delivery System and reporting techniques

Proc Tabulate multi-dimensional table percentages

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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: 8,868

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

View solution in original 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,035

Proc Tabulate multi-dimensional table percentages

How about:

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: 8,868

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

Posted in reply to Cynthia_sas

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: 8,868

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

Proc Tabulate multi-dimensional table percentages

Posted in reply to Cynthia_sas

Thanks so much, that worked!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1049 views
  • 0 likes
  • 4 in conversation