Combining Tables Vertically with PROC SQL

This tutorial explains how to combine / append data sets vertically with PROC SQL. Suppose you have two data sets and we need to combine these two datasets vertically. For example, if a dataset A contains 10 records and dataset B contains 10 records. I want combined dataset would contain 20 records.

Create data sets in SAS

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;

Input Data Sets

1. UNION Operator

It displays all rows from both the tables and removes duplicate records from the combined dataset. By adding ALL keyword, it allows duplicate rows in the combined dataset.

Important Point

UNION is performed by position not by column name. Hence, common columns in each SELECT statement should be in the same order.  If CORR keyword is included, PROC SQL matches the columns by name.

ALL Keyword

ALL keyword allows duplicates in the concatenated dataset.

CORR Keyword

CORR keyword tells SAS to match the columns in table by name and not by position. Columns that do not match by name are excluded from the result table, except for the OUTER UNION operator

UNION Operator

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;

OUTER UNION CORR

3. Except Operator

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

Except Operator

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

Except ALL

It allows duplicate records in the combined dataset and does not remove duplicates.

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

Except ALL

Except CORR

It displays only columns that have the same name (or common) in both the tables.

Except CORR

It returns all unique rows in the first table (based on the common column) 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

Except ALL Corr

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;

SQL : Intersect Operator

Proc SQL Tutorials : 15 Proc SQL Tutorials

Spread the Word!
Share Share Tweet Subscribe

Related Posts

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 has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.

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