Basic syntax for vertical operation of SQL
procSQL;Select * fromtable1Set-operator <all > <corr>Select * fromtable2Set-operator <all > <corr>Select * fromTable3;
1: Several set operators Except, Intersect, Union, Outerjoin
The Except, Intersect, and union three set symbols are unique processing by default and are performed in two steps when unique processing occurs
1. PROC SQL eliminates duplicate (nonunique) rows in the tables.
2. PROC SQL selects the rows that meet the criteria and, where requested, overlays columns.
When the operation is performed with unique and duplicate lines, only the second step, ignoring the first step, is required.
Except, Intersect, and union three set symbols operate on a column- by-position basis , regardless of the name of the corresponding column (alias-based alias). When overlapping, name the top of the first table, and if no name is named with the name of the column that corresponds to the second table.
The data type of the first and second table columns must be the same, otherwise it cannot be manipulated
Outerjoin does not overwrite columns
Corr and All keywords
2.1:except (default column corresponds to location action)
By default, this process is carried out in two steps
1: Make a unique, delete the duplicate rows in one.
2: Delete One of the rows from one to the other by comparing one to the other.
Add the ALL keyword separately
Do not make a unique step and keep filtering as it is. (Omit the first step to improve efficiency)
Add Corr keyword separately
Merge by column name, and delete all column names.
Perform a unique step, and then delete each of the peers in one
Plus all and Corr
1: First delete column by column name
2: Do not make a unique, keep duplicate rows, and then delete the rows in one in one.
2.2:intersect Grammar, the specific do not understand the data
2.3:union syntax, do not know the details of the data Union to sort non-all
2.4:outer Union (It has all the nature, so it cannot be used with all)
1:selecting all rows (both unique and nonunique) from both tables
2:not overlaying columns.
Syntax without a keyword
Using the CORR keyword
SQL in SAS (5) Vertical operation Datasets Except, Intersect, Union, Outerjoin