topic Re: Need help with Joining Multiple Tabels in SAS Procedures

Need help with Joining Multiple Tabels

InfoAlisaA — Sat, 25 Feb 2012 20:40:24 GMT

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

Re: Need help with Joining Multiple Tabels

art297 — Sat, 25 Feb 2012 20:48:10 GMT

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?

Need help with Joining Multiple Tabels

InfoAlisaA — Sat, 25 Feb 2012 21:08:58 GMT

I just posted an update to this, and I just need the one that is associated with the Employee_Addresses data set.

Re: Need help with Joining Multiple Tabels

PGStats — Sat, 25 Feb 2012 21:31:21 GMT

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

Need help with Joining Multiple Tabels

InfoAlisaA — Sat, 25 Feb 2012 21:45:19 GMT

Wouldn't it be easier to do an inline query instead of using all of those joins?

Need help with Joining Multiple Tabels

art297 — Sat, 25 Feb 2012 22:04:25 GMT

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;

Need help with Joining Multiple Tabels

InfoAlisaA — Sat, 25 Feb 2012 22:23:51 GMT

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?

Need help with Joining Multiple Tabels

InfoAlisaA — Sat, 25 Feb 2012 22:26:24 GMT

Could you also explain to me why you used coalese in your query?

Thanks again for your help!!:smileylaugh:

Re: Need help with Joining Multiple Tabels

PGStats — Sat, 25 Feb 2012 23:30:57 GMT

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:

I find it preferable to do explicit joins when inner joins are combined with left joins which cannot be specified as implicit joins (with simple where conditions). In any case, explicit joins require almost the same amount of typing as implicit joins .
The coalesce function takes care of the cases where the left join doesnt find the employee's manager. In a hierartical list like that, it is obvious that the "top" manager will not have anyone listed as his/her manager. The coalesce takes care of that case.
I replaced the HAVING clause with a WHERE clause because the HAVING clause is meant to be applied to the result of aggregation functions. In some SQL parsers, HAVING clauses are not even accepted without a GROUP BY clause.
The INTCK function will calculate the exact years of service (your calculation was probably good enough for most purposes but it wasn't exact ). Check the function's documentation.

Need help with Joining Multiple Tabels

InfoAlisaA — Sun, 26 Feb 2012 00:17:33 GMT

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.