DATA Step, Macro, Functions and more

Self join in SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Self join in SAS

Hi All

 

Recently i attended an interview and the interviewer asked me a really tricky question which i am not able to solve till yet , kindly provide a solutions for this 

 

The questions is like this .... 

 

i have to take out the employees whose salary is morethan their manager's salary

Note: the salary column refers to the employee's salary and the manager ID referes to the manager of the employee 

 

 

Employee IDManager IDSalary
11139509
21539849
3912274
4747687
5210285
6634802
7511532
8322624
9117640

 


Accepted Solutions
Solution
‎09-18-2017 02:40 PM
Super User
Super User
Posts: 7,050

Re: Self join in SAS

Posted in reply to soham_sas

Seems simple.

proc sql ;
  create table want as 
    select a.*,b.salary as msalary
    from have a left join have b
    on a.mid = b.eid
    where a.salary > b.salary and not missing(b.salary)
  ;
quit;
Obs    Eid    Mid    Salary    msalary

 1      8      3      22624     12274
 2      7      5      11532     10285
 3      4      7      47687     11532

View solution in original post


All Replies
Super User
Super User
Posts: 7,955

Re: Self join in SAS

Posted in reply to soham_sas

And what can you not do here?  What about:

proc sql;  
  create table WANT as 
  select * 
  from   HAVE
  where SALARY > (select SALARY from HAVE where MANAGER_ID=EMPLOYEE_ID);
quit;

Of course that assumes there is only one record per manager/employee (all I can assume from what you posted).  Basically you merge on the manager data based on the two ids and compare, can do the same in datastep.

Occasional Contributor
Posts: 12

Re: Self join in SAS

@RW9by using this code the employee Id 1 ,2 and 4 is coming , but the required result is 4,7 and 8

 

as 4's manager is 7 and 4's salary is more than 7's salary and like wise for emp_ID 7 and 8

 

please help for the same as i am stuck on this to get the logic of how to resolve this issue

Solution
‎09-18-2017 02:40 PM
Super User
Super User
Posts: 7,050

Re: Self join in SAS

Posted in reply to soham_sas

Seems simple.

proc sql ;
  create table want as 
    select a.*,b.salary as msalary
    from have a left join have b
    on a.mid = b.eid
    where a.salary > b.salary and not missing(b.salary)
  ;
quit;
Obs    Eid    Mid    Salary    msalary

 1      8      3      22624     12274
 2      7      5      11532     10285
 3      4      7      47687     11532
Occasional Contributor
Posts: 12

Re: Self join in SAS

Thanks @Tom !!! you made my day :-)

 

Thanks a ton (Y)

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 114 views
  • 0 likes
  • 3 in conversation