SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

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