The set operator OUTER UNION concatenates the results of the queries by the following:

We can demonstrate how OUTER UNION works when used alone and with the keyword CORR. The ALL keyword is not used with OUTER UNION because this operator's default action is to include all rows in output.

Suppose you want to display all rows from both of the tables One and Two, without overlaying columns. The PROC SQL set operation that includes the OUTER UNION operator, the two tables, and the output are shown below:

proc sql;
   select *
      from one
   outer union
   select *
      from two;

In the output, the columns are not overlaid. Instead, all four columns from both tables are displayed. Each row of output contains missing values in the two columns that correspond to the other table.

The output from the preceding set operation contains two columns with the same name. To overlay the columns with a common name, add the CORR keyword to the set operation:

proc sql;
   select *
      from one
   outer union corr
   select *
      from two;

The output from the modified set operation contains only three columns, because the two columns named X are overlaid.

There are many business situations that require two or more tables to be concatenated. For example, suppose you want to display the employee numbers, job codes, and salaries of all mechanics working for an airline. The mechanic job has three levels and there is a separate table containing data for the mechanics at each level: Sasuser.Mechanicslevel1, Sasuser.Mechanicslevel2, and Sasuser.Mechanicslevel3. These tables all contain the same three columns.

The following PROC SQL step uses two OUTER UNION operators to concatenate the tables, and the CORR keyword to overlay the columns that have common names:

proc sql; 
   select *
      from sasuser.mechanicslevel1
   outer union corr
   select *
      from sasuser.mechanicslevel2
   outer union corr
   select *
      from sasuser.mechanicslevel3;