BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Patty
Calcite | Level 5

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
Cynthia_sas
Diamond | Level 26

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

7 REPLIES 7
Tim_SAS
Barite | Level 11

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
Super User

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
Calcite | Level 5

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

Cynthia_sas
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26

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
Calcite | Level 5

Thanks so much, that worked!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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