Proc Tabulate multi-dimensional table percentages

[Patty]

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.

[Tim_SAS]

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!

[Ksharp]

How about:

PROC TABULATE DATA=TEST;

     CLASS VARIABLE1 VARIABLE 2 VARIABLE3 VARIABLE4;

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

RUN;

Ksharp

[Patty]

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

[Cynthia_sas]

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

[Patty]

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

[Cynthia_sas · Accepted solution]

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

[Patty]

Thanks so much, that worked!