Chapter Contents |
Previous |
Next |
The SQL Procedure |
See also: | table-expression , Query Expressions (Subqueries) , and In-Line Views |
table-expression <set-operator table-expression>... |
INTERSECT <CORRESPONDING> <ALL> |
OUTER UNION <CORRESPONDING> |
UNION <CORRESPONDING> <ALL> |
EXCEPT <CORRESPONDING> <ALL> |
Query Expressions and Table Expressions |
Set Operators |
A query-expression with set operators is evaluated as follows.
Set operators follow this order of precedence unless they are overridden by parentheses in the expression(s): INTERSECT is evaluated first. OUTER UNION, UNION, and EXCEPT have the same level of precedence.
PROC SQL performs set operations even if the tables or views that are referred to in the table-expressions do not have the same number of columns. The reason for this is that the ANSI Standard for SQL requires that tables or views involved in a set operation have the same number of columns and that the columns have matching data types. If a set operation is performed on a table or view that has fewer columns than the one(s) with which it is being linked, PROC SQL extends the table or view with fewer columns by creating columns with missing values of the appropriate data type. This temporary alteration enables the set operation to be performed correctly.
CORRESPONDING (CORR) Keyword |
For example, when performing a set operation on two table-expressions, PROC SQL matches the first specified column-name (listed in the SELECT clause) from one table-expression with the first specified column-name from the other. If CORR is omitted, PROC SQL matches the columns by ordinal position.
ALL Keyword |
OUTER UNION |
For example, the following query expression concatenates the ME1 and ME2 tables but does not overlay like-named columns. OUTER UNION of ME1 and ME2 Tables shows the result.
proc sql; title 'ME1 and ME2: OUTER UNION'; select * from me1 outer union select * from me2;
OUTER UNION of ME1 and ME2 Tables
To overlay columns with the same name, use the CORRESPONDING keyword.
proc sql; title 'ME1 and ME2: OUTER UNION CORRESPONDING'; select * from me1 outer union corr select * from me2;In the resulting concatenated table, notice the following:
1120
appear in the output.
UNION |
Columns are appended by position in the tables, regardless of the column names. However, the data type of the corresponding columns must match or the union will not occur. PROC SQL issues a warning message and stops executing.
The names of the columns in the output table are the names of the columns from the first table-expression unless a column (such as an expression) has no name in the first table-expression. In such a case, the name of that column in the output table is the name of the respective column in the second table-expression.
In the following example, PROC SQL combines the two tables:
proc sql; title 'ME1 and ME2: UNION'; select * from me1 union select * from me2;In the following example, ALL includes the duplicate row from ME1. In addition, ALL changes the sorting by specifying that PROC SQL make one pass only. Thus, the values from ME2 are simply appended to the values from ME1.
proc sql; title 'ME1 and ME2: UNION ALL'; select * from me1 union all select * from me2;See Combining Two Tables for another example.
EXCEPT |
In the following example, the IN_USA table contains flights to cities within and outside the USA. The OUT_USA table contains flights only to cities outside the USA. This example returns only the rows from IN_USA that are not also in OUT_USA:
proc sql; title 'Flights from IN_USA'; select * from in_usa except select * from out_usa;
INTERSECT |
proc sql; title 'Flights from IN_USA and OUT_USA'; select * from in_usa intersect select * from out_usa;
Chapter Contents |
Previous |
Next |
Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.