Practical Problem-Solving with PROC SQL

Computing Percentages within Subtotals

You want to analyze answers to a survey question to determine how each state responded. Then you want to compute the percentage of each answer that a given state contributed. For example, what percentage of all NO responses came from North Carolina?

Background Information

There is one input table, called SURVEY, that contains the following data (the first ten rows are shown):

Input Table for Computing Subtotal Percentages (Partial Output)

                      Sample Data for Subtotal Percentages

                             Obs    State    Answer

                               1     NY       YES  
                               2     NY       YES  
                               3     NY       YES  
                               4     NY       YES  
                               5     NY       YES  
                               6     NY       YES  
                               7     NY       NO   
                               8     NY       NO   
                               9     NY       NO   
                              10     NC       YES

Solution

Use the following PROC SQL code to compute the subtotal percentages:

proc sql;
   title1 'Survey Responses';
   select survey.Answer, State, count(State) as Count,
          calculated Count/Subtotal as Percent format=percent8.2
   from survey,
        (select Answer, count(*) as Subtotal from survey
            group by Answer) as survey2
   where survey.Answer=survey2.Answer
   group by survey.Answer, State;
quit;

PROC SQL Output That Computes Percentages within Subtotals

                                Survey Responses

                     Answer    State        Count   Percent
                     --------------------------------------
                     NO        NC              24   38.71% 
                     NO        NY               3    4.84% 
                     NO        PA              18   29.03% 
                     NO        VA              17   27.42% 
                     YES       NC              20   37.04% 
                     YES       NY               6   11.11% 
                     YES       PA               9   16.67% 
                     YES       VA              19   35.19%

How It Works

This solution uses a subquery to calculate the subtotal counts for each answer. The code joins the result of the subquery with the original table and then uses the calculated state count as the numerator and the subtotal from the subquery as the denominator for the percentage calculation.

The query uses a GROUP BY clause to combine the data so that the calculation is performed for State within each answer.

group by survey.Answer, State;

Top of Page