DATA Step, Macro, Functions and more

how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID

Reply
Occasional Contributor
Posts: 16

how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID

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
      
EmpidJobIdDepIdFin_LocDojupdated Date
1212I52125/06/1725/07/17
1312B73425/06/1725/05/17
1456R35525/06/1725/06/17
1234B46525/06/1725/06/18
2245N7825/06/1625/06/18
3343N55625/06/1502/03/14
3788K45625/06/1522/03/15
3567T54425/06/1522/03/15
4788K45625/06/1522/03/15

 

Expected answer

 

EXPECTED DATA  
        
EmpidJobIdDepIdFin_LocDojupdated DateStart_DateEnd_Date
1312B73425/01/1725/05/1725/01/1725/05/17
1456R35525/01/1725/06/1726/05/1725/06/17
1212I52125/01/1725/07/1726/06/1725/07/17
1234B46525/01/1725/06/1826/07/1725/06/18
2245N7825/06/1625/06/1825/06/1625/06/18
3343N55625/06/1502/03/1625/06/1502/03/16
3788K45625/06/1522/03/1703/03/1622/03/17
3567T54425/06/1522/03/1823/03/1722/03/18
4788K45625/06/1522/03/1525/06/1522/03/15
Respected Advisor
Posts: 2,993

Re: how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID

Posted in reply to srinivaschary

Explain how you derived the value of Start_Date for Empid=1.

--
Paige Miller
Valued Guide
Posts: 590

Re: how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID

Posted in reply to srinivaschary

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

Thanks,
Suryakiran
Occasional Contributor
Posts: 16

Re: how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID

Posted in reply to SuryaKiran

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     
      
EmpidJobIdDepIdFin_LocDojupdated Date
1212I52125/06/1725/07/17
1312B73425/06/1725/05/17
1456R35525/06/1725/06/17
1234B46525/06/1725/06/18

 

 

EXPECTED DATA     
      
EmpidJobIdDepIdFin_LocStart_DateEnd_Date
1312B73425/01/1725/05/17
1456R35526/05/1725/06/17
1212I52126/06/1725/07/17
1234B46526/07/1725/06/18
Valued Guide
Posts: 564

Re: how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID

Posted in reply to srinivaschary

So for JobId 456, 212 and 234 start-date is after end-date ... a bit weird.

Ask a Question
Discussion stats
  • 4 replies
  • 61 views
  • 0 likes
  • 4 in conversation