Output 6.8 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;
Counting Duplicate Rows in a Table
Problem
You want to count the number of duplicate rows in a table and generate an output
column that shows how many times each row occurs.
Background Information
There is one input table, called Duplicates, that contains the following data:
data Duplicates;
input LastName $ FirstName $ City $ State $;
datalines;
Smith John Richmond Virginia
Johnson Mary Miami Florida
Smith John Richmond Virginia
Reed Sam Portland Oregon
Davis Karen Chicago Illinois
Davis Karen Chicago Illinois
Counting Duplicate Rows in a Table 187
Thompson Jennifer Houston Texas
Smith John Richmond Virginia
Johnson Mary Miami Florida
;
proc print data=Duplicates;
title 'Sample Data for Counting Duplicates';
run;
Output 6.9 Sample Input Table for Counting Duplicates
Solution
Use the following PROC SQL code to count the duplicate rows:
proc sql;
title 'Duplicate Rows in Duplicates Table';
select *, count(*) as Count
from Duplicates
group by LastName, FirstName, City, State
having count(*) > 1;
Output 6.10 PROC SQL Output for Counting Duplicates
188 Chapter 6 Practical Problem-Solving with PROC SQL

Get SAS 9.4 SQL Procedure User's Guide, Second Edition, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.