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

Many queries can be formulated as joins or subqueries. Although the PROC SQL
query optimizer changes some subqueries to joins, a join is generally more efficient
to process.
Combining Queries with Set Operators
Working with Two or More Query Results
PROC SQL can combine the results of two or more queries in various ways by using the
following set operators:
UNION
produces all unique rows from both queries.
EXCEPT
produces rows that are part of the first query only.
INTERSECT
produces rows that are common to both query results.
OUTER UNION
concatenates the query results.
The operator is used between the two queries, for example:
select columns from table
set-operator
select columns from table;
Place a semicolon after the last SELECT statement only. Set operators combine columns
from two queries based on their position in the referenced tables without regard to the
individual column names. Columns in the same relative position in the two queries must
have the same data types. The column names of the tables in the first query become the
column names of the output table. For information about using set operators with more
than two query results, see the Chapter 7, “SQL Procedure,” on page 215. The following
optional keywords give you more control over set operations:
ALL
does not suppress duplicate rows. When the keyword ALL is specified, PROC SQL
does not make a second pass through the data to eliminate duplicate rows. Thus,
using ALL is more efficient than not using it. ALL is not allowed with the OUTER
UNION operator.
CORRESPONDING (CORR)
overlays columns that have the same name in both tables. When used with EXCEPT,
INTERSECT, and UNION, CORR suppresses columns that are not in both tables.
Each set operator is described and used in an example based on the following two tables.
Combining Queries with Set Operators 103
Output 3.32 Tables Used in Set Operation Examples
Whereas join operations combine tables horizontally, set operations combine tables
vertically. Therefore, the set diagrams that are included in each section are displayed
vertically.
Producing Unique Rows from Both Queries (UNION)
The UNION operator combines two query results. It produces all the unique rows that
result from both queries. That is, it returns a row if it occurs in the first table, the second,
or both. UNION does not return duplicate rows. If a row occurs more than once, then
only one occurrence is returned.
proc sql;
title 'A UNION B';
select * from sql.a
union
select * from sql.b;
104 Chapter 3 Retrieving Data from Multiple Tables
Output 3.33 Producing Unique Rows from Both Queries (UNION)
You can use the ALL keyword to request that duplicate rows remain in the output.
proc sql;
title 'A UNION ALL B';
select * from sql.a
union all
select * from sql.b;
Output 3.34 Producing Rows from Both Queries (UNION ALL)
Producing Rows That Are in Only the First Query Result (EXCEPT)
The EXCEPT operator returns rows that result from the first query but not from the
second query. In this example, the row that contains the values 3 and three exists in the
first query (table A) only and is returned by EXCEPT.
Combining Queries with Set Operators 105
proc sql;
title 'A EXCEPT B';
select * from sql.a
except
select * from sql.b;
Output 3.35 Producing Rows That Are in Only the First Query Result (EXCEPT)
Note that the duplicated row in Table A containing the values 2 and two does not appear
in the output. EXCEPT does not return duplicate rows that are unmatched by rows in the
second query. Adding ALL keeps any duplicate rows that do not occur in the second
query.
proc sql;
title 'A EXCEPT ALL B';
select * from sql.a
except all
select * from sql.b;
Output 3.36 Producing Rows That Are in Only the First Query Result (EXCEPT ALL)
Producing Rows That Belong to Both Query Results (INTERSECT)
The INTERSECT operator returns rows from the first query that also occur in the
second.
proc sql;
title 'A INTERSECT B';
select * from sql.a
intersect
select * from sql.b;
106 Chapter 3 Retrieving Data from Multiple Tables
Output 3.37 Producing Rows That Belong to Both Query Results (INTERSECT)
The output of an INTERSECT ALL operation contains the rows produced by the first
query that are matched one-to-one with a row produced by the second query. In this
example, the output of INTERSECT ALL is the same as INTERSECT.
Concatenating Query Results (OUTER UNION)
The OUTER UNION operator concatenates the results of the queries. This example
concatenates tables A and B.
proc sql;
title 'A OUTER UNION B';
select * from sql.a
outer union
select * from sql.b;
Output 3.38 Concatenating the Query Results (OUTER UNION)
Combining Queries with Set Operators 107

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