Computing Percentages within Subtotals :: SAS(R) 9.3 SQL Procedure User's Guide

Problem

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):

data survey;
    input State $ Answer $ @@;
    datalines;
NY YES NY YES NY YES NY YES NY YES NY YES NY NO  NY NO  NY NO  NC YES 
NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES
NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC NO
NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO
NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO
NC NO  NC NO  NC NO  PA YES PA YES PA YES PA YES PA YES PA YES PA YES
PA YES PA YES PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  
PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO
VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES
VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA NO
VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO
VA NO  VA NO  VA NO  VA NO  VA NO  VA NO
;

proc print data=Survey(obs=10);
  title 'Sample Data for Subtotal Percentages';
run;

Input Table for Computing Subtotal Percentages (Partial Output)

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

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;