Using the UNION Set Operator
A Brief Overview
The set operator UNION does both of the following:
-
selects unique rows from both tables
-
overlays columns
Figure 4.5 UNION Set Operator Relationship
Example: Using the UNION Operator Alone
To display all rows
from the tables Col1 and Col2 that are unique in the combined set
of rows from both tables, use a PROC SQL set operation that includes
the UNION operator.
proc sql;
select *
from certadv.col1 union
select *
from certadv.col2;
quit;
Output 4.16 PROC SQL Query Result Illustration
With the UNION operator,
PROC SQL first concatenates and sorts the rows from the two tables,
and eliminates any duplicate rows. In this example, two rows are eliminated:
the second row in table Col1 is a duplicate of the first row, and
the fourth row in table Col2 matches the fifth row in table Col1.
All remaining rows, the unique rows, are included in the output. The
columns are overlaid by position.
Example: Using the Keyword ALL with the UNION Operator
When the keyword ALL is added to the UNION operator,
the output displays all rows from both tables, both unique and duplicate.
proc sql;
select *
from certadv.col1 union all
select *
from certadv.col2;
quit;
Output 4.17 PROC SQL Query Result Illustration
When the keyword ALL
is used, PROC SQL does not remove duplicates or sort the rows. The
output now includes the two duplicate rows that were eliminated in
the previous example: the second row in table Col1 and the fourth
row in table Col2. Note that order of the rows in this output differs
from the order of the rows in the output from the previous set operation.
Example: Using the Keyword CORR with the UNION Operator
To display all rows from the tables Col1 and Col2 that
are unique in the combined set of rows from both tables, based on
columns that have the same name rather than the same position, add
the keyword CORR after the set operator.
proc sql;
select *
from certadv.col1 union corr
select *
from certadv.col2;
quit;
Output 4.18 PROC SQL Query Result Illustration
X is the only column
name that is common to both tables, so X is the only column that PROC
SQL examines and displays in the output. In the combined set of rows
from the two tables, there are duplicates of the values
1, 2,
and 3, and these duplicate rows are
eliminated from the output. The output displays the six unique values
of X.Example: Using the Keywords ALL and CORR with the UNION Operator
If the keywords ALL and CORR are used together, the
UNION operator displays all rows in the two tables both unique and
duplicate, based on the columns that have the same name. In this example,
the output displays all 12 values for X, the one column that has the
same name in both tables.
proc sql;
select *
from certadv.col1 union all corr
select *
from certadv.col2;
quit;
Output 4.19 PROC SQL Query Result Illustration
Example: Using the UNION Set Operator
Suppose you are generating
a report based on data from a health clinic. You want to display the
results of individual patient stress tests taken in 2017, followed
by the results from stress tests taken in 2018. To do this, you use
the UNION operator to combine the tables Certadv.Stress17 and Certadv.Stress18.
These two tables have similar structure:
-
Both tables contain nine columns that have the same names.
-
Each row contains data for an individual patient.
You are not sure whether
the tables contain duplicate records, but you do not want duplicates
in your output. Because the tables have the same column structure,
you can overlay the columns by position, and the keyword CORR is
not necessary.
proc sql;
select *
from certadv.stress17 union
select *
from certadv.stress18;
quit;
Output 4.20 PROC SQL Query Result: UNION Set Operator
Tip
If you can determine that
these tables have no duplicate records, you could add the keyword
ALL to speed up processing by avoiding an extra pass through the data.Example: Using a UNION Operator and Summary Functions
Suppose you want to
display the following summarized data for members of a frequent-flyer
program: total points earned, total points used, and total miles traveled.
All three values can be calculated from columns in the table Certadv.Frequentflyers
by using summary functions.
You might wonder why
set operations are needed when only one table is involved. If you
wanted to display the three summarized values horizontally, in three
separate columns, you could solve the problem without a set operation,
using the following simple SELECT statement:
proc sql;
select sum(pointsearned) format=comma12.
label='Total Points Earned',
sum(pointsused) format=comma12.
label='Total Points Used',
sum(milestraveled) format=comma12.
label='Total Miles Traveled'
from certadv.frequentflyers;
quit;
Output 4.21 PROC SQL Query Result
Assume, however, that
you want the three values to be displayed vertically in a single column.
To generate this output, you create three different queries on the
same table, and then use two UNION set operators to combine the three
query results:
proc sql;
title 'Points and Miles Traveled';
title2 'by Frequent Flyers';
select 'Total Points Earned:',
sum(PointsEarned) format=comma12.
from certadv.frequentflyers union
select 'Total Points Traveled:',
sum(MilesTraveled) format=comma12.
from certadv.frequentflyers union
select 'Total Points Used:',
sum(PointsUsed) format=comma12.
from certadv.frequentflyers
;
quit;
Each SELECT clause defines
two columns: a character constant as a label and the summarized value.
The output is shown below.
Output 4.22 PROC SQL Query Result: Using a UNION Operator and Summary Functions
Last updated: October 16, 2019
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.