BookmarkSubscribeRSS Feed
Geo-
Quartz | Level 8

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

Given the Employee table, write data step that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

 

data detail;

   input id $6

         name $8.

         salary 

         ManagerId $;
 

   datalines;
  1  Joe   70000 3   
  2  Henry 80000 4   
  3  Sam   60000 .
  4  Max   90000 .
;
run;

6 REPLIES 6
ballardw
Super User

See if this gets you started. Your data step as posted does not generate the data you think it does.

 

data detail;
   input id : $6.
         name :$8.
         salary 
         ManagerId $
;
datalines;
1  Joe   70000 3   
2  Henry 80000 4   
3  Sam   60000 .
4  Max   90000 .
;
run;

proc sql;
   create table want as
   select a.name as employee, b.name as manager, a.salary as empsal, b.salary as managersalary
   from detail as a
        left join
        detail as b
        on a.managerid = b.id;
quit;

Reeza
Super User

What part of the question don't you understand that you need help with? There's a SQL solution above, but the instructions mention data step, so why a data step? 

 

The steps are the same:

 

1. Merge data with itself

In a data step, you need to rename the variables that are not key ID variables. 

2. Flag if Manager is greater than Employee. 

 

Geo-
Quartz | Level 8
yes..data step is a must..would you mind tell me the exact code?thanks
ballardw
Super User

@Geo- wrote:
yes..data step is a must..would you mind tell me the exact code?thanks

Why data step?

If this is homework then what have you tried?

If it is not homework why do you want what will be a more complicated solution?

 

 

Geo-
Quartz | Level 8
just some practice that have to be done but means little from the way of using data step.BTW,your sql is wrong in this situation
Reeza
Super User

@Geo- wrote:
just some practice that have to be done but means little from the way of using data step.BTW,your sql is wrong in this situation

 

The SQL isn't wrong, but it's not the full answer to your question, intentionally.

 

There are two steps to this problem, a merge and a filter (WHERE/IF) operation. 

 

Which step do you not know how to do? 

 

See the video tutorials below for each topic:

 

Merge data:

https://video.sas.com/detail/video/4572997800001/merging-sas-tables-in-a-data-step

 

Filter data:

https://video.sas.com/detail/video/4573016761001/filtering-a-sas-table-in-a-data-step

 

If you still have issues after reviewing these videos (6 minutes roughly in total) post the code you've tried and the log. 

 


@Geo- wrote:
just some practice that have to be done but means little from the way of using data step.BTW,your sql is wrong in this situation

 

 

 

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 806 views
  • 0 likes
  • 3 in conversation