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

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

9 REPLIES 9
art297
Opal | Level 21

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?

InfoAlisaA
Calcite | Level 5

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

PGStats
Opal | Level 21

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
InfoAlisaA
Calcite | Level 5

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

art297
Opal | Level 21

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;

InfoAlisaA
Calcite | Level 5

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?

InfoAlisaA
Calcite | Level 5

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

Thanks again for your help!!:smileylaugh:

PGStats
Opal | Level 21

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
InfoAlisaA
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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