SAS : Join on Multiple Columns

This post explains how to join two data sets (tables) based on multiple variables (columns) in SAS.

Creating two data sets (tables)

Let's create two SAS datasets for demonstration purposes.

data def;
input a b $ d;
cards;
123 X 5
441 D 2
;
run;

data abc;
input a b $ c;
cards;
123 A 5
123 B 6
123 X 8
441 C 2
441 D 5
;
run;

Task : Suppose you need to join these two data sets (tables) based on variables a and b.

Method 1 : SQL Joins

The following PROC SQL code performs a SQL join operation between two tables "def" (aliased as "x") and "abc" (aliased as "y") based on the conditions that the columns "a" and "b" are equal in both tables. It creates a table named "xyz".

proc sql noprint;
create table xyz as
select * from
def x left join abc y
on x.a = y.a and x.b = y.b;
quit;

Method 2 : Data Step Merge Statements

The following SAS code uses the merge statement to combine data from two input datasets, "def" and "abc", based on the common variables "a" and "b".

data xyz1;
merge def(in=x) abc(in=Y);
by a b;
if x;
run;

Output

Spread the Word!
Share Share Tweet

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.

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