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

How It Works
This solution uses a query that performs the following:
selects all columns
counts all rows
groups all of the rows in the Duplicates table by matching rows
excludes the rows that have no duplicates
Note: You must include all of the columns in your table in the GROUP BY clause to
find exact duplicates.
Expanding Hierarchical Data in a Table
Problem
You want to generate an output column that shows a hierarchical relationship among
rows in a table.
Background Information
There is one input table, called Employees, that contains the following data:
data Employees;
input ID $ LastName $ FirstName $ Supervisor $;
datalines;
1001 Smith John 1002
1002 Johnson Mary None
1003 Reed Sam None
1004 Davis Karen 1003
1005 Thompson Jennifer 1002
1006 Peterson George 1002
1007 Jones Sue 1003
1008 Murphy Janice 1003
1009 Garcia Joe 1002
;
proc print data=Employees;
title 'Sample Data for Expanding a Hierarchy';
run;
Expanding Hierarchical Data in a Table 189
Output 6.11 Sample Input Table for Expanding a Hierarchy
You want to create output that shows the full name and ID number of each employee
who has a supervisor, along with the full name and ID number of that employee's
supervisor.
Solution
Use the following PROC SQL code to expand the data:
proc sql;
title 'Expanded Employee and Supervisor Data';
select A.ID label="Employee ID",
trim(A.FirstName)||' '||A.LastName label="Employee Name",
B.ID label="Supervisor ID",
trim(B.FirstName)||' '||B.LastName label="Supervisor Name"
from Employees A, Employees B
where A.Supervisor=B.ID and A.Supervisor is not missing;
190 Chapter 6 Practical Problem-Solving with PROC SQL
Output 6.12 PROC SQL Output for Expanding a Hierarchy
How It Works
This solution uses a self-join (reflexive join) to match employees and their supervisors.
The SELECT clause assigns aliases of A and B to two instances of the same table and
retrieves data from each instance. From instance A, the SELECT clause performs the
following:
selects the ID column and assigns it a label of Employee ID
selects and concatenates the FirstName and LastName columns into one output
column and assigns it a label of Employee Name
From instance B, the SELECT clause performs the following:
selects the ID column and assigns it a label of Supervisor ID
selects and concatenates the FirstName and LastName columns into one output
column and assigns it a label of Supervisor Name
In both concatenations, the SELECT clause uses the TRIM function to remove trailing
spaces from the data in the FirstName column, and then concatenates the data with a
single space and the data in the LastName column to produce a single character value for
each full name.
trim(A.FirstName)||' '||A.LastName label="Employee Name"
When PROC SQL applies the WHERE clause, the two table instances are joined. The
WHERE clause conditions restrict the output to only those rows in table A that have a
supervisor ID that matches an employee ID in table B. This operation provides a
supervisor ID and full name for each employee in the original table, except for those
who do not have a supervisor.
where A.Supervisor=B.ID and A.Supervisor is not missing;
Note: Although there are no missing values in the Employees table, you should check
for and exclude missing values from your results to avoid unexpected results. For
example, if there were an employee with a blank supervisor ID number and an
employee with a blank ID, then they would produce an erroneous match in the
results.
Expanding Hierarchical Data in a Table 191

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