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?

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

```                      Sample Data for Subtotal Percentages

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
```

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
quit;```

```                                Survey Responses

--------------------------------------
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%
```

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;`

 Previous Page | Next Page | Top of Page