Combining Tables Vertically with PROC SQL

Deepanshu Bhalla 16 Comments ,

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;
Sample SAS Dataset
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.
SAS : 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;
SAS SQL : OUTER UNION CORR
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.

SAS SQL : EXCEPT Operator
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; 
SAS SQL : EXCEPT ALL Operator
EXCEPT CORR Operator

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

SAS : EXCEPT CORR Operator

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;
SAS SQL : 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;
SAS SQL : Intersect Operator
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

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.

Post Comment 16 Responses to "Combining Tables Vertically with PROC SQL"
  1. Thanks for providing easy to understand examples and language which helps t grab complex things much easier.

    ReplyDelete
  2. It's reallllllly easy to understand

    ReplyDelete
  3. Its very good and understandable....

    ReplyDelete
  4. Hi sir,
    Are you clinical sas programmer?

    ReplyDelete
  5. Thank you for the easy understand example and explanation. It is very good for learners to go through

    ReplyDelete
  6. Hi
    Can you please explain the difference between inner join and intersect with same example.

    ReplyDelete
    Replies
    1. inner join

      inner join is a equi join and we can use non equi join also. when condition is true , matched records are displayed

      intersect is used to derived common records in both table
      A table;
      id
      1
      2
      3
      4

      B table;
      id
      1
      3
      4

      inner join :

      select * from a, b where a.id=b.id;
      select * from a inner join b on a.id=b.id;

      intersect

      select id from a intersect select id from b;
      1
      3
      4

      Delete
  7. can you explain CORR with some brief example

    ReplyDelete
  8. THANKS A TON FOR MAKING IT CLEAR AND EASY TO LEARN

    ReplyDelete
  9. Thanks for sharing it with us! Can you please guide me how to append multiple files using a sql function at once other than typing "Union all corr select * from C"? Thank you.

    ReplyDelete
  10. I have doubt that, in the example datasets variable names are different then how it is combining them ?
    does while combining it only sees the row values and not variable names ?

    ReplyDelete
Next → ← Prev