Practical Problem-Solving with PROC SQL |

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

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;

Copyright © 2008 by SAS Institute Inc., Cary, NC, USA. All rights reserved.