O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Notice that OUTER UNION does not overlay columns from the two tables. To overlay
columns in the same position, use the CORRESPONDING keyword.
proc sql;
title 'A OUTER UNION CORR B';
select * from sql.a
outer union corr
select * from sql.b;
Output 3.39 Concatenating the Query Results (OUTER UNION CORR)
Producing Rows from the First Query or the Second Query
There is no keyword in PROC SQL that returns unique rows from the first and second
table, but not rows that occur in both. Here is one way that you can simulate this
operation:
(query1 except query2)
union
(query2 except query1)
This example shows how to use this operation.
proc sql;
title 'A EXCLUSIVE UNION B';
(select * from sql.a
except
select * from sql.b)
union
(select * from sql.b
except
108 Chapter 3 Retrieving Data from Multiple Tables
select * from sql.a);
Output 3.40 Producing Rows from the First Query or the Second Query
The first EXCEPT returns one unique row from the first table (table A) only. The second
EXCEPT returns one unique row from the second table (table B) only. The middle
UNION combines the two results. Thus, this query returns the row from the first table
that is not in the second table, as well as the row from the second table that is not in the
first table.
Combining Queries with Set Operators 109
110 Chapter 3 Retrieving Data from Multiple Tables

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required