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.
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
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!
How about:
PROC TABULATE DATA=TEST;
CLASS VARIABLE1 VARIABLE 2 VARIABLE3 VARIABLE4;
TABLE VARIABLE1* (VARIABLE2 ALL VARIABLE3 ALL) ALL ,VARIABLE4 *(N COLPCTN);
RUN;
Ksharp
Thanks, gave that a try but I got the same results as before. Will keep trying.
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
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...
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
Thanks so much, that worked!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: