Proc SQL Self Join Tutorial

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;

Proc SQL Tutorials : 15 Proc SQL Tutorials

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

Related Posts:

1 Response to "Proc SQL Self Join Tutorial"

  1. Awesome explanation...
    Understood it clearly... Thanks a lot for posting..

    ReplyDelete

Next → ← Prev