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 1998 (which are
stored in Table A), followed by the results from stress tests taken
in 1999 (which are stored in Table B). Instead of combining the table
rows horizontally, as you would in a PROC SQL join, you want to combine
the table rows vertically (one on top of the other).
When you need to select
data from multiple tables and combine the tables vertically, PROC
SQL can be an efficient alternative to using other SAS procedures
or the DATA step. In a PROC SQL set operation, you use one of four
set operators (EXCEPT, INTERSECT, UNION, and OUTER UNION) to combine
tables (and views) vertically by combining the results of two queries:
proc sql;
select *
from a
set-operator
select *
from b;
Each set operator combines
the query results in a different way.
In this chapter, you
learn how to use the various set operators, with or without the optional
keywords ALL and CORR (CORRESPONDING), to combine the results of multiple
queries.
Note: In this chapter, the references
to tables are also applicable to views, unless otherwise noted.