BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
soham_sas
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

@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
Super User Tom
Super User

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
Quartz | Level 8

Thanks @Tom !!! you made my day 🙂

 

Thanks a ton (Y)

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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