Proc SQL Self Join Tutorial

Live Online Training : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects
- Scenario Based Questions
- Job Placement Assistance
- Get 20% off till July 14, 2017

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

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

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