Help using Base SAS procedures

Need help with Joining Multiple Tabels

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

Need help with Joining Multiple Tabels

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


Accepted Solutions
Solution
‎02-25-2012 05:04 PM
PROC Star
Posts: 7,363

Need help with Joining Multiple Tabels

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;

View solution in original post


All Replies
PROC Star
Posts: 7,363

Re: Need help with Joining Multiple Tabels

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?

Frequent Contributor
Posts: 90

Need help with Joining Multiple Tabels

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

Respected Advisor
Posts: 4,646

Re: Need help with Joining Multiple Tabels

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

PG
Frequent Contributor
Posts: 90

Need help with Joining Multiple Tabels

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

Solution
‎02-25-2012 05:04 PM
PROC Star
Posts: 7,363

Need help with Joining Multiple Tabels

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;

Frequent Contributor
Posts: 90

Need help with Joining Multiple Tabels

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?

Frequent Contributor
Posts: 90

Need help with Joining Multiple Tabels

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

Thanks again for your help!!:smileylaugh:

Respected Advisor
Posts: 4,646

Re: Need help with Joining Multiple Tabels

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 Smiley Happy.
  • 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 Smiley Wink). Check the function's documentation.

PG

PG
Frequent Contributor
Posts: 90

Need help with Joining Multiple Tabels

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 609 views
  • 6 likes
  • 3 in conversation