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

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

Related Posts:

31 Responses to "Lesson 2 : PROC SQL : Joins"

  1. Its Very Useful... Thanks a lot sharing..!

    ReplyDelete
    Replies
    1. Thank you for stopping by. Glad you found the article useful :-)

      Delete
  2. Well explained! Keep up the good work :-)

    ReplyDelete
  3. Thanks Deepanshu for sharing this valuable knowledge. Its really a treasure for beginners like me.

    ReplyDelete
  4. Well explained..Its really useful especially for beginners. Thank u for sharing.

    ReplyDelete
  5. really very helpful ! i have one doubt explain the ten procedures which were used in SQL?

    ReplyDelete
  6. Very Helpful and easy to understand
    Thanks!

    ReplyDelete
  7. Extremely helpful, thank you very much !

    ReplyDelete
  8. Moumita Chakraborty22 August 2015 at 21:08

    Really very helpful but I found a small typo. In the full join SQL code wont it be 'full join' instead of 'right join'?

    ReplyDelete
    Replies
    1. Thanks for pointing it out, typo now corrected!

      Delete
  9. Love your site...I'm glad that I found this site...

    ReplyDelete
  10. Excellent! good explanation with codes . . . very useful!

    ReplyDelete
  11. Loved your explanation..Very useful for SAS beginners..Keep going

    ReplyDelete
  12. Hi Deepanshu,

    in all the joins it is mentioned

    (Common columns in both the data sets + uncommon columns from data set A + uncommon columns from data set B).

    is this true????

    ReplyDelete
    Replies
    1. If you include "*" keyword, it would select variables from both the tables.

      Delete
  13. full join code should be like this
    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;

    ReplyDelete
    Replies
    1. Thanks for pointing it out. I have corrected the code. Cheers!

      Delete
  14. full join Data Step code should be

    Data dummy;
    Merge A (IN = X) B (IN=Y);
    by ID;
    if x=1 or y=1;
    run;

    ReplyDelete
    Replies
    1. Please let me know if I am wrong

      Delete
    2. By default, MERGE Statement performs FULL JOIN so "if x=1 or y=1;" is not required. Please let me know incase any query(s) Thanks!

      Delete
    3. The output record will be prepared either from the record of A or B, hence the statement "if x=1 or y=1;" will not be required to mention and it will be by default.

      Delete
  15. Well explained about joins. Very use full for the beginners.

    ReplyDelete
  16. Did you read the first paragraph 'Advantages of PROC SQL Joins over Data Step Merging'? That's answer of your first question. Second question is also discussed in the article. If you have duplicates in any of the tables, you would get cartesian product of the duplicate records.

    ReplyDelete
  17. Was very useful.!! Well explained.

    ReplyDelete
  18. This comment has been removed by the author.

    ReplyDelete
  19. What about Outer joins? How it will be prepared using PROC SQL?

    ReplyDelete

Next → ← Prev