CSULB_STAT475_handout24 - STAT 475 Chapter 4 COMBINING...

STAT 475 Chapter 4 COMBINING TABLES VERTICALLY
USING PROC SQL
Reference
SAS Certification Prep Guide: Advanced Programming for SAS 9
PROC SQL may be used to select data from multiple tables and to combine the
tables vertically (that is, pasting one after another). The following four set
operations are distinguished:

EXCEPT (selects unique rows from the first table that are not found in the
second table)
*******
*** ********
** ***
Syntax
proc sql;
select *
from table1
except
select *
from table2 ;
quit;

INTERSECT (selects unique rows that are common to both tables)
********
*********
Syntax
1

proc sql;
select *
from table1
intersect
select *
from table2 ;
quit;

UNION (selects unique rows from one or both tables)
*******
*** ********
** *********
***********
************
************
************
*******
Syntax
proc sql;
select *
from table1
union
select *
from table2 ;
quit;

OUTER UNION (selects all rows from both tables)
*******
*** ********
** *********
***********
********
******
***********
*************
*************
**********
Syntax
2

proc sql;
select *
from table1
outer union
select *
from table2 ;
quit;
Example. Consider two sets
Below are queries for the four set operators and their relative outputs.