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

The following examples demonstrate how OUTER UNION works when used alone and with the keyword CORR. The keyword ALL 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 Col1 and Col2, without overlaying columns.

proc sql;
   select *
      from certadv.col1 outer union
   select *
      from certadv.col2;
quit;

In the output, the columns have not been 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 keyword CORR to the set operation:

proc sql;
   select *
      from certadv.col1 outer union corr
   select *
      from certadv.col2
;
quit;

Suppose you want to display the employee ID 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: Certadv.Mechanicslevel1, Certadv.Mechanicslevel2, and Certadv.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 keyword CORR to overlay the columns that have common names:

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