Self join in SAS

[soham_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 ID	Manager ID	Salary
1	11	39509
2	15	39849
3	9	12274
4	7	47687
5	2	10285
6	6	34802
7	5	11532
8	3	22624
9	1	17640

[RW9]

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.

[soham_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

[Tom · Accepted solution]

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

[soham_sas]

Thanks @Tom !!! you made my day 🙂

Thanks a ton (Y)