Combining Tables Vertically with PROC SQL

This tutorial explains how to append datasets using PROC SQL in SAS, along with examples.

Sample SAS Datasets

The following SAS datasets will be used to explain examples in this tutorial.

data dat1;
input x y;
cards;
1 6
1 6
1 7
6 4
7 6
8 7
;
run;

data dat2;
input x z;
cards;
1   5
4   2
3   4
6   4
6   5
5   8
;
run;

1. UNION Operator

It displays all rows from both the tables and removes duplicate records from the combined dataset.

Important Points

1. UNION is performed by position not by column name. Hence, common columns in each SELECT statement must be in the same order.
2. If CORR keyword is added after UNION, PROC SQL matches the columns by name. Columns that do not match by name are excluded from the result table, except for the OUTER UNION operator.
3. ALL keyword allows duplicates in the concatenated dataset.

proc sql;
create table out7 as
select *
from dat1
UNION
select *
from dat2;
quit; 

proc sql;
create table out8 as
select *
from dat1
UNION ALL
select *
from dat2;
quit;

proc sql;
create table out9 as
select *
from dat1
UNION CORR
select *
from dat2;
quit;

2. OUTER UNION CORR

It appends (concatenates) two tables. It is equivalent to SET statement in Data Step. It allows duplicates in the concatenated table. The ALL keyword is not required with OUTER UNION.

proc sql;
create table out10 as
select *
from dat1
OUTER UNION CORR
select *
from dat2;
quit;

3. EXCEPT Operator

It returns unique rows from the first table that are not found in the second table. (Non-matched Rows). It removes duplicate records (where all columns in the results are the same) - row 2nd in table1.

proc sql;
create table out1 as
select *
from dat1
EXCEPT
select *
from dat2;
quit; 

EXCEPT ALL Operator

It allows duplicate records in the combined dataset. In simple words, it does not remove duplicates.

proc sql;
create table out2 as
select *
from dat1
EXCEPT ALL
select *
from dat2;
quit; 

EXCEPT CORR Operator

It displays only columns that have the same name in both the tables.

It returns all unique rows in the first table that do not appear in the second table.

proc sql;
create table out3 as
select *
from dat1
EXCEPT CORR
select *
from dat2;
quit;

Except ALL CORR Operator

The ALL keyword means that SQL will keep all the duplicated rows.

proc sql;
create table out3 as
select *
from dat1
EXCEPT ALL CORR
select *
from dat2;
quit;

4. INTERSECT Operator

It selects unique rows that are common to both the tables.

proc sql;
create table out5 as
select *
from dat1
INTERSECT
select *
from dat2;
quit;

Related Posts

Proc SQL Tutorials : Top 15 Proc SQL Tutorials

Spread the Word!
Share Share Tweet

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn