Hi Guys,
I have a peoplesoft HR data (Emp_ID,Emp_Name,EMP_Status,PosnID,...Dept_ID, EFFT_D,EXPY_D)..in this table i have list of each employees details with their historiy movements..i mean if employee moves to another team his/her details changes with ,PosnID,...Dept_ID, EFFT_D,EXPY_D...
like below...
EMPL_I | EMPL_M | SRNM_M | FRST_M | SCND_M | EMPL_STUS_C | POSN_I | DEPT_I | EFFT_D | EXPY_D |
12345 | XXX | XXX | ACT | 20011765 | 009926 | 03May1999 | 19May2002 | ||
12345 | XXX | XXX | ACT | 20011771 | 003352 | 20May2002 | 12Jan2003 | ||
12345 | XXX | XXX | ACT | 20011771 | 003352 | 13Jan2003 | 29May2003 | ||
12345 | XXX | XXX | ACT | 20011771 | 010889 | 30May2003 | 21Aug2003 | ||
12345 | XXX | XXX | ACT | 20011771 | 010889 | 22Aug2003 | 31Aug2003 | ||
12345 | XXX | XXX | ACT | 20029804 | 012381 | 01Sep2003 | 20Oct2004 | ||
12345 | XXX | XXX | ACT | 20029525 | 012113 | 21Oct2004 | 01May2005 | ||
12345 | XXX | XXX | ACT | 20029804 | 012408 | 02May2005 | 05Sep2005 | ||
12345 | XXX | XXX | ACT | 20029804 | 012408 | 06Sep2005 | 15Jun2006 | ||
12345 | XXX | XXX | ACT | 20029804 | 012408 | 16Jun2006 | 03Nov2006 | ||
12345 | XXX | XXX | TRM | 20029804 | 012408 | 04Nov2006 | 31Dec2099 | ||
23456 | YYY | ACT | 20034907 | 013624 | 25Feb2008 | 08Oct2008 | |||
23456 | YYY | ACT | 20040662 | 008554 | 09Oct2008 | 26Apr2009 | |||
23456 | YYY | ACT | 20040662 | 008554 | 27Apr2009 | 30Jun2010 | |||
23456 | YYY | ACT | 20038079 | 008554 | 01Jul2010 | 12Jun2011 | |||
23456 | YYY | ACT | 20038054 | 008733 | 13Jun2011 | 16Sep2012 | |||
23456 | YYY | ACT | 20000395 | 008573 | 17Sep2012 | 31Jul2014 | |||
23456 | YYY | ACT | 20000395 | 008554 | 01Aug2014 | 26Oct2014 | |||
23456 | YYY | ACT | 20133898 | 014717 | 27Oct2014 | 08Jun2015 | |||
23456 | YYY | TRM | 20010522 | 000729 | 15Aug2015 | 31Dec2099 |
My monthly report is picks only by considering the employee EXPY_D = 31DEC2099....so the report captures the recent department for that employee...
Issue is :When an employee moves onto new role.the hierarchy moves with them and the credit goes to latest(new) department..
How can i create the dataset that can consdier the role and department based on reporting Date/Review Date?
Appreciate your help and response...Please let me know if its not clear...thanks
Regards,
RS
Do you have some other dataset that is being joined to this one that lists the credits per employee per date? At the moment you've only shown the employee's hierarchy history, but that doesn't let us know exactly how the data is being used to generate the report.
In general you would likely be looking at joining the credits data to the entire employee history not just the latest record, and not just on the employee id matching but also the date range. That should let you pull the group for the employee for the given date.
Hey,
thanks for quick reply.
Am joining with the the peoplesoft data(above listed) with the other dataset that has empl_n(employee name)
i have only employee name to join and can get review date to check if it falls within that dates...
like below,...
Review_Date | EMPL_N |
31Jan2017 | XXX |
18Jan2017 | XXX |
25Jan2017 | XXX |
01Feb2017 | XXX |
06Feb2017 | YYY |
13Feb2017 | YYY |
07Mar2017 | YYY |
Later am joining to department dataset that has dept_i(dept code)
unfortunately i dont have any unique variables to consider? even for more than 2 employee has same name...it picks only the latest...
hope it is clear...thank you
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.