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
SAS Super FREQ

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
SAS Super FREQ

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
SAS Super FREQ

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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