BookmarkSubscribeRSS Feed
BaalaRaaji
Quartz | Level 8

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_IEMPL_MSRNM_MFRST_MSCND_MEMPL_STUS_CPOSN_IDEPT_IEFFT_DEXPY_D
12345XXX XXX ACT2001176500992603May199919May2002
12345XXX XXX ACT2001177100335220May200212Jan2003
12345XXX XXX ACT2001177100335213Jan200329May2003
12345XXX XXX ACT2001177101088930May200321Aug2003
12345XXX XXX ACT2001177101088922Aug200331Aug2003
12345XXX XXX ACT2002980401238101Sep200320Oct2004
12345XXX XXX ACT2002952501211321Oct200401May2005
12345XXX XXX ACT2002980401240802May200505Sep2005
12345XXX XXX ACT2002980401240806Sep200515Jun2006
12345XXX XXX ACT2002980401240816Jun200603Nov2006
12345XXX XXX TRM2002980401240804Nov200631Dec2099
23456YYY   ACT2003490701362425Feb200808Oct2008
23456YYY   ACT2004066200855409Oct200826Apr2009
23456YYY   ACT2004066200855427Apr200930Jun2010
23456YYY   ACT2003807900855401Jul201012Jun2011
23456YYY   ACT2003805400873313Jun201116Sep2012
23456YYY   ACT2000039500857317Sep201231Jul2014
23456YYY   ACT2000039500855401Aug201426Oct2014
23456YYY   ACT2013389801471727Oct201408Jun2015
23456YYY   TRM2001052200072915Aug201531Dec2099

 

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

2 REPLIES 2
JChambo
Fluorite | Level 6

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.

BaalaRaaji
Quartz | Level 8

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_DateEMPL_N
31Jan2017XXX
18Jan2017XXX
25Jan2017XXX
01Feb2017XXX
06Feb2017YYY
13Feb2017YYY
07Mar2017YYY

 

 

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

 

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!

How to Concatenate Values

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.

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
  • 2 replies
  • 598 views
  • 0 likes
  • 2 in conversation