SAS SQL : Comparing two tables

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 

Best Online Course : Practical SQL Guide for Beginners

Proc SQL Tutorials : 15 Proc SQL Tutorials


SAS Tutorials : 100 Free SAS Tutorials

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

Related Posts:

0 Response to "SAS SQL : Comparing two tables"

Post a Comment

Next → ← Prev