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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.