SAS SQL : Comparing two tables

Live Online Training : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects
- Scenario Based Questions
- Job Placement Assistance
- Get 10% off till Sept 25, 2017
- Batch starts from October 8, 2017

Suppose you have two data sets (tables), oldfile and newfile. You want to compare these two datasets and want to see the updated rows and common rows in both the tables.

I . Creating two data sets (tables) - oldfile and newfile
data oldfile;
input id First$;
cards;
5463 Olsen
6574 Hogan
7896 Bridge
4352 Anson
5674 Leach
7902 Wilson
9786 Fabes
;

data newfile;
input id First$;
cards;
5463 Olsen
6574 Hogan
7896 Bridge
4352 Anson
5671 Leach
7900 Wilson
9786 Sampo
2112 Ramav
;
II . Comparing two tables - Old File and New File

Comparing two datasets
The rows that are highlighted in red are updated rows that do not exist in Old file.

The EXCEPT operator returns rows from the first query that are not part of the second query. It returns updated rows that are not found in the old file.
/* Comparing two tables - Updated data*/
proc sql;
title "Updated Rows";
select * from newfile
except
select * from oldfile;
quit;
Common Rows in both the tables 

The INTERSECT operator returns common rows in both the tables.
proc sql;
title "Common Rows";
select * from newfile
intersect
select * from oldfile;
quit;

Data Step Merge : Comparing two datasets

We can compare two datasets with data step merge statement. First we need to sort both the datasets by all the variables and then merge by _all_.
proc sort data = oldfile;
by _all_;
run;
proc sort data = newfile;
by _all_;
run;
Updated Rows 
data merged;
merge oldfile(in=a) newfile(in=b);
by _all_;
if a = 0;
run;
Common Rows 
data merged;
merge oldfile(in=a) newfile(in=b);
by _all_;
if a and b;
run;
Related Article : Combine Tables Vertically with PROC SQL 

Proc SQL Tutorials : 15 Proc SQL Tutorials


SAS Tutorials : 100 Free SAS Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

3 Responses to "SAS SQL : Comparing two tables"

  1. I also want to compare two datasets but want to output those attributes for which the values are different only.

    ReplyDelete
  2. e.g i have two datasets

    One:-
    a b c
    1 2 3
    2 3 4
    3 4 5
    Two:-
    1 3 3
    2 3 4
    3 4 5

    so in the output i want to show that b attribute in the 1st row have different value. which wil help me to identify the pattern of wrong output and will help me to work only on those attributes for which my output is not matching with the master data.

    ReplyDelete
  3. please send your suggestions as i need the solution very urgently. i am also working on that but not able to capture the erroneous attribute. Please email @pawan.pvs@gmail.com if someone finds something.

    Thanks a lot in advance!!!

    ReplyDelete

Next → ← Prev