SQL in SAS (5) Vertical operation Datasets Except, Intersect, Union, Outerjoin

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