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 |
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
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.
@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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.