Hi,
I have given sample data set below. this table have employeeid,doj and updated date. from this table i want start_date and End_date using DOJ and updated date group by employeeID.
SAMPLE DATA | |||||
Empid | JobId | DepId | Fin_Loc | Doj | updated Date |
1 | 212 | I5 | 21 | 25/06/17 | 25/07/17 |
1 | 312 | B7 | 34 | 25/06/17 | 25/05/17 |
1 | 456 | R3 | 55 | 25/06/17 | 25/06/17 |
1 | 234 | B4 | 65 | 25/06/17 | 25/06/18 |
2 | 245 | N | 78 | 25/06/16 | 25/06/18 |
3 | 343 | N5 | 56 | 25/06/15 | 02/03/14 |
3 | 788 | K4 | 56 | 25/06/15 | 22/03/15 |
3 | 567 | T5 | 44 | 25/06/15 | 22/03/15 |
4 | 788 | K4 | 56 | 25/06/15 | 22/03/15 |
Expected answer
EXPECTED DATA | |||||||
Empid | JobId | DepId | Fin_Loc | Doj | updated Date | Start_Date | End_Date |
1 | 312 | B7 | 34 | 25/01/17 | 25/05/17 | 25/01/17 | 25/05/17 |
1 | 456 | R3 | 55 | 25/01/17 | 25/06/17 | 26/05/17 | 25/06/17 |
1 | 212 | I5 | 21 | 25/01/17 | 25/07/17 | 26/06/17 | 25/07/17 |
1 | 234 | B4 | 65 | 25/01/17 | 25/06/18 | 26/07/17 | 25/06/18 |
2 | 245 | N | 78 | 25/06/16 | 25/06/18 | 25/06/16 | 25/06/18 |
3 | 343 | N5 | 56 | 25/06/15 | 02/03/16 | 25/06/15 | 02/03/16 |
3 | 788 | K4 | 56 | 25/06/15 | 22/03/17 | 03/03/16 | 22/03/17 |
3 | 567 | T5 | 44 | 25/06/15 | 22/03/18 | 23/03/17 | 22/03/18 |
4 | 788 | K4 | 56 | 25/06/15 | 22/03/15 | 25/06/15 | 22/03/15 |
Explain how you derived the value of Start_Date for Empid=1.
Did you miss something to explain us?
If you want to compare previous row values to current row values then try using LAG() function. eg: Start_Date=LAG(End_Date)+1
Sorry for late reply,
Actually what exatly i need based on employee id i need to create start date and end date using doj and updated date for eg below.
SAMPLE DATA | |||||
Empid | JobId | DepId | Fin_Loc | Doj | updated Date |
1 | 212 | I5 | 21 | 25/06/17 | 25/07/17 |
1 | 312 | B7 | 34 | 25/06/17 | 25/05/17 |
1 | 456 | R3 | 55 | 25/06/17 | 25/06/17 |
1 | 234 | B4 | 65 | 25/06/17 | 25/06/18 |
EXPECTED DATA | |||||
Empid | JobId | DepId | Fin_Loc | Start_Date | End_Date |
1 | 312 | B7 | 34 | 25/01/17 | 25/05/17 |
1 | 456 | R3 | 55 | 26/05/17 | 25/06/17 |
1 | 212 | I5 | 21 | 26/06/17 | 25/07/17 |
1 | 234 | B4 | 65 | 26/07/17 | 25/06/18 |
So for JobId 456, 212 and 234 start-date is after end-date ... a bit weird.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.