Lesson 2 : PROC SQL : Joins

This tutorial is designed for beginners who want to get started with PROC SQL Joins. It explains different types of joins and the equivalent data step merge code for these joins.

Lesson 1 : Proc SQL Fundamentals with 20 Examples

Advantages of PROC SQL Joins over Data Step Merging

1. PROC SQL joins do not require sorted tables (data sets), while you need to have two data sets sorted when using MERGE statement
2. PROC SQL joins do not require that common variable have the same name in the data sets you are joining, while you need to have common variable name listed in BY option when using MERGE statement. 
3. PROC SQL joins can use comparison operators other than the equal sign (=).
4. PROC SQL can handle many to many relationship well whereas Data Step Merge do not.

1. Cross Join / Cartesian product

The Cartesian product returns a number of rows equal to the product of all rows (observations) in all the tables (data sets) being joined. For example, if the first table has 10 rows and the second table has 10 rows, there will be 100 rows (10 * 10) in the merged table (data set).

Create these two data sets into SAS

Data A;
Input ID Name$ Height;
cards;
1 A 1
3 B 2
5 C 2
7 D 2
9 E 2
;
run;

Data B;
Input ID Name$ Weight;
cards;
2 A 2
4 B 3
5 C 4
7 D 5
;
run;

Cartesian Product : SQL Code

PROC SQL;
Create table dummy as
Select * from A as x cross join B as y;
Quit;

Cartesian or Cross Product

Key takeaways

1. Since the first data set has 5 rows and the second data set has 4 rows, there are 20 rows (5 * 4) in the merged data set.

2. The 'as' keyword (aka alias) is used to assign a table a temporary name.

3. Since the ID values of the first data set is different than the ID values of the second data set, the ID given in the joined data set is misleading.

2. Inner Join

It returns rows common to both tables (data sets). If we select * keyword in the query, the final merged file would have number of columns equal to (Common columns in both the data sets + uncommon columns from data set A + uncommon columns from data set B).

Venn Diagram : Inner Join

PROC SQL;
Create table dummy as
Select * from A as x, B as y
where x.ID = y.ID;
Quit;

Inner Join

Explanation

Since the above case is of type INNER JOIN, it returns values 5 and 7 from the variable ID in the combined table as these two values are common in both the datasets A and B

Another way to write the above code - 

PROC SQL;
Create table dummy as
Select * from A as x inner join B as y
On x.ID = y.ID;
Quit;

Both the codes produce same result.

Inner Join : Data Step Code

Data dummy;        
Merge A (IN = X) B (IN=Y);
by ID;
If X and Y;
run;

3. Left Join

It returns all rows from the left table with the matching rows from the right table.

Left Join Venn Diagram

PROC SQL;
Create table dummy as
Select * from A as x left join B as y
On x.ID = y.ID;
Quit;

Left Join

Explanation

Since the above case is of type LEFT JOIN, it returns all rows from the table (dataset) A with the matching rows from the dataset B.

Left Join : Data Step Code

Data dummy;        
Merge A (IN = X) B (IN=Y);
by ID;
If X ;
run;

4. Right Join

It returns all rows from the right table that do not match any row with the left-hand table, and the matched rows from the left-hand table.

Right Join Venn Diagram

PROC SQL;

Create table dummy as

Select * from A as x right join B as y

On x.ID = y.ID;

Quit;

Right Join

Note : The right-hand table ID values are missing in the merged table.

To add the missing right hand table ID values to a right join, you can use the SQL COALESCE function. The COALESCE function returns the first non-missing argument.

proc sql;
create table dummy as
select coalesce (x.ID,y.ID) as ID, coalesce (x.name,y.name) as name,height,weight
from a as x right join b as y
on x.id = y.id;
quit;

Right Join with Coalesce

Explanation

Since the above case is of type RIGHT JOIN, it returns all rows from the table (dataset) B with the matching rows from the dataset A.

Right Join : Data Step Code

Data dummy;        
Merge A (IN = X) B (IN=Y);
by ID;
If Y ;
run;

5. Full Join

It returns all rows from the left table and from the right table.

Full Join

 Key takeaway : The FULL JOIN suffers the same difficulty as the RIGHT JOIN. Namely, the common variable values are lost from the right-hand data set. The COALESCE function can solve this difficulty.

proc sql;
create table dummy as
select coalesce (x.ID,y.ID) as ID, coalesce (x.name,y.name) as name,height,weight
from a as x full join b as y
on x.id = y.id;
quit;

Full Join with Coalesce

Explanation

Since the above case is of type FULL JOIN, it returns all rows from the table (dataset) A and B.

Full Join : Data Step Code

Data dummy;        
Merge A B;
by ID;
run;

By default, MERGE statement performs full join so IN variables are not required. 

One to Many Relationship : Duplicate Values in Primary Key

Join : One to Many Relationship

SQL Join will return Cartesian Product if duplicate values are found in primary key (common column). In this example, it returns cartesian product of missing values in the "ID" column. Since dataset A has 3 missing values and dataset B has 1 missing value, there are 3 (3*1) missing values in the merged dataset.

Data Step MERGE statement will return the maximum number of missing values in the primary key in both the tables. In this case, it would return 3 missing values i.e. max(3,1).

Example 2 : One to Many Relationship

PROC SQL : One to Many Relationship

How about six rows of  value 5 in the combined table?

When duplicates, PROC SQL returns cartesian product i.e. product of both the tables. In dataset A, we have 2  5s and 3 5s in dataset B. So, it returns (2x3 = 6) 5s in the combined table.

How to refer to permanent library in SQL Joins

PROC SQL;
Create table dummy as
Select * from readin.A as x left join readin.B as y
On x.ID = y.ID;
Quit;

Related Article - Data Step Merge 

Proc SQL Tutorials : 15 Proc SQL Tutorials

SAS Tutorials : 100 Free SAS Tutorials

Spread the Word!
Share Share Tweet

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