O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required