topic Re: Self join in SAS in SAS Programming

Self join in SAS

soham_sas — Mon, 11 Sep 2017 11:04:08 GMT

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

Re: Self join in SAS

RW9 — Mon, 11 Sep 2017 11:25:56 GMT

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.

Re: Self join in SAS

soham_sas — Mon, 18 Sep 2017 17:23:20 GMT

@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

Re: Self join in SAS

Tom — Mon, 18 Sep 2017 18:20:13 GMT

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

Re: Self join in SAS

soham_sas — Mon, 18 Sep 2017 18:44:01 GMT

Thanks @Tom !!! you made my day 🙂

Thanks a ton (Y)