• 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
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:
produces all unique rows from both queries.
produces rows that are part of the first query only.
produces rows that are common to both query results.
concatenates the query results.
The operator is used between the two queries, for example:
select columns from table
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:
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
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