Hello Everyone,
I am having some problems with this program that I am working on for my Advanced SAS studies.
I need to create a report that displays the employee's name, their years of service, and the employee manager's name.
I have three tables I need to combine:
orion.Employee_Addresses contains
* Employee_ID
* Employee_Name
orion.Employee_Payroll contains
* Employee_ID
* Employee_Hire_Date
orion.Employee_Organization contains
* Employee_ID
* Manager_ID (Employee_ID of the person's manager which is also the same as their Employee_ID)
I've gotten this code so far:
libname orion 'C:\SAS\'; * Creating libref Orion;
proc sql;
title1 'Employees with more than 30 years of service';
title2 'as of December 31, 2007';
select a.Employee_Name, int(('31DEC2007'd-p.Employee_Hire_Date)/365.25) as YOS label='Years of Service'
from orion.Employee_Addresses as a,
orion.Employee_Payroll as p
where calculated YOS>30;
quit;
This is the query that I have to identify the managers:
proc sql;
select distinct a.Employee_Name,a.Employee_ID,
from orion.Employee_Addresses as a,
orion.Employee_Organization as o
where a.Employee_ID=o.Manager_ID;
quit;
I combined the two to get this query:
proc sql;
title1 'Employees with more than 30 years of service';
title2 'as of December 31, 2007';
select distinct a.Employee_Name,int(('31DEC2007'd-p.Employee_Hire_Date)/365.25) as YOS label='Years of Service',
Manager_Name.Employee_Name as ManagerName label = 'Manager Name'
from orion.Employee_Addresses as a,
orion.Employee_Payroll as p,
(select a.Employee_Name
from orion.Employee_Addresses as a,
orion.Employee_Organization as o
where a.Employee_ID=o.Manager_ID) as Manager_Name
having YOS>30
order by ManagerName, YOS desc,Employee_Name;
quit;
But now it is not filtering to the 71 employees that I want and pulling every employee and saying that they have greater than 30 years of service.
Could someone give me a little help with this?
Thanks!
Alisa
I'm not SQLsavvy enough to answer your last question. Regardless, the only thing (other than a quit statement) that you would have to add to PGStats suggested code, in order to get what you wanted, is the part about years of service. i.e.:
proc sql;
select A.Employee_Name, P.Employee_Hire_Date format=date0.,
int(('31DEC2007'd-p.Employee_Hire_Date)/365.25)
as YOS label='Years of Service',
coalesce(A2.Employee_Name,"Unknown") as Manager_Name
from orion.Employee_Addresses AS A
inner join orion.Employee_Payroll as P
on A.Employee_ID=P.Employee_ID
inner join orion.Employee_Organization as O
on A.Employee_ID=O.Employee_ID
left join orion.Employee_Addresses AS A2
on O.Manager_ID=A2.Employee_ID
having calculated YOS>30
;
quit;
A question for you:
Are there multiple records for any Employee_IDs in any of the three files and, if so, which one do you want?
I just posted an update to this, and I just need the one that is associated with the Employee_Addresses data set.
Assuming that employee_ID is a unique key to all three datasets, you could start with something like this :
proc sql;
select A.Employee_Name, P.Employee_Hire_Date, coalesce(A2.Employee_Name,"Unknown") as Manager_Name
from orion.Employee_Addresses AS A inner join orion.Employee_Payroll as P on A.Employee_ID=P.Employee_ID
inner join orion.Employee_Organization as O on A.Employee_ID=O.Employee_ID
left join orion.Employee_Addresses AS A2 on O.Manager_ID=A2.Employee_ID;
the key here is referencing Employee_Addresses twice in the query
PG
Wouldn't it be easier to do an inline query instead of using all of those joins?
I'm not SQLsavvy enough to answer your last question. Regardless, the only thing (other than a quit statement) that you would have to add to PGStats suggested code, in order to get what you wanted, is the part about years of service. i.e.:
proc sql;
select A.Employee_Name, P.Employee_Hire_Date format=date0.,
int(('31DEC2007'd-p.Employee_Hire_Date)/365.25)
as YOS label='Years of Service',
coalesce(A2.Employee_Name,"Unknown") as Manager_Name
from orion.Employee_Addresses AS A
inner join orion.Employee_Payroll as P
on A.Employee_ID=P.Employee_ID
inner join orion.Employee_Organization as O
on A.Employee_ID=O.Employee_ID
left join orion.Employee_Addresses AS A2
on O.Manager_ID=A2.Employee_ID
having calculated YOS>30
;
quit;
Thanks so much!! :smileylaugh:
Could you explain to me why it was better to use the joins instead of doing an inline query?
Also, why did you use coalesce as a part of the query?
Could you also explain to me why you used coalese in your query?
Thanks again for your help!!:smileylaugh:
You might get slightly better marks with the query :
proc sql;
select A.Employee_Name, P.Employee_Hire_Date format=date0.,
INTCK("YEAR", P.Employee_Hire_Date, "31DEC2007"d, "CONTINUOUS")
as YOS label='Years of Service',
coalesce(A2.Employee_Name, "Unknown") as Manager_Name
from orion.Employee_Addresses AS A
inner join orion.Employee_Payroll as P
on A.Employee_ID=P.Employee_ID
inner join orion.Employee_Organization as O
on A.Employee_ID=O.Employee_ID
left join orion.Employee_Addresses AS A2
on O.Manager_ID=A2.Employee_ID
where calculated YOS > 30
;
Explanations:
PG
Thanks so much your help and explanations. I am studying for this stuff on my own as part of my senior project in college, so it is hard when all I have is a book and some PowerPoint slides to help get me through.
I would have never thought of doing inner and left joins for this problem. I was just trying to brute force it with inline queries.
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 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.