Proc SQL Self Join Tutorial

Deepanshu Bhalla 2 Comments ,
This tutorial explains how to apply self join in SQL query.

Example 1 : Find out Manager
Suppose you have data for employees. It comprises of employees' name, ID and manager ID. You need to find out manager name against each employee ID.

Proc SQL : Self Join
data example2;
input Name $ ID ManagerID;
cards;
Smith 123 456
Robert 456 .
William 222 456
Daniel 777 222
Cook 383 222
;
run;
SQL Query : Self Join
proc sql;
create table want as
select a.*, b.Name as Manager
from example2 as a left join example2 as b
on a.managerid = b.id;
quit;
Example 2 : Find out Manager of Manager
Suppose you have data for employees. It comprises of employees' name, ID and manager ID. You need to find out manager of manager's name against each employee ID.
Self Join SQL Query

data example22;
input Name $ ID ManagerID;
cards;
Smith 123 456
Robert 456 777
William 222 123
Daniel 777 .
Cook 383 456
;
run;
proc sql;
create table want as
select a.Name, a.ID, a.managerid, b.ManagerID as ManagerofManagerID
from  example22 a left join example22 b
on a.managerid = b.id;
quit;
proc sql;
create table want2 as
select a.*, b.Name as ManagerofManagerName
from want as a left join want as b
on a.ManagerofManagerID = b.id;
quit;

Example 3 : Find out Grand Son
data example22;
input Parent $ Child $;
cards;
A1  B1
A2  B3
B1  C1
C1  D2
B3  C3
;
run;
proc sql;
create table want as
select a.Parent as GrandParent, b.Child as GrandChild
from  example22 a left join example22 b
on a.child = b.parent;
quit;
Related Posts
Spread the Word!
Share
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.

Post Comment 2 Responses to "Proc SQL Self Join Tutorial"
  1. Awesome explanation...
    Understood it clearly... Thanks a lot for posting..

    ReplyDelete
  2. Nice work.
    Can you elaborate joins using Proc Format and SAS Hashing

    ReplyDelete
Next → ← Prev