topic Re: how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID in SAS Programming https://communities.sas.com/t5/SAS-Programming/how-to-split-Start-Date-and-End-Date-using-DOJ-and-Updated-date/m-p/473253#M121419 <P>So for JobId 456, 212 and 234 start-date is after end-date ... a bit weird.</P> Tue, 26 Jun 2018 07:31:16 GMT andreas_lds 2018-06-26T07:31:16Z how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID https://communities.sas.com/t5/SAS-Programming/how-to-split-Start-Date-and-End-Date-using-DOJ-and-Updated-date/m-p/473088#M121355 <P>Hi,</P><P>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.</P><TABLE><TBODY><TR><TD>SAMPLE DATA</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Empid</TD><TD>JobId</TD><TD>DepId</TD><TD>Fin_Loc</TD><TD>Doj</TD><TD>updated Date</TD></TR><TR><TD>1</TD><TD>212</TD><TD>I5</TD><TD>21</TD><TD>25/06/17</TD><TD>25/07/17</TD></TR><TR><TD>1</TD><TD>312</TD><TD>B7</TD><TD>34</TD><TD>25/06/17</TD><TD>25/05/17</TD></TR><TR><TD>1</TD><TD>456</TD><TD>R3</TD><TD>55</TD><TD>25/06/17</TD><TD>25/06/17</TD></TR><TR><TD>1</TD><TD>234</TD><TD>B4</TD><TD>65</TD><TD>25/06/17</TD><TD>25/06/18</TD></TR><TR><TD>2</TD><TD>245</TD><TD>N</TD><TD>78</TD><TD>25/06/16</TD><TD>25/06/18</TD></TR><TR><TD>3</TD><TD>343</TD><TD>N5</TD><TD>56</TD><TD>25/06/15</TD><TD>02/03/14</TD></TR><TR><TD>3</TD><TD>788</TD><TD>K4</TD><TD>56</TD><TD>25/06/15</TD><TD>22/03/15</TD></TR><TR><TD>3</TD><TD>567</TD><TD>T5</TD><TD>44</TD><TD>25/06/15</TD><TD>22/03/15</TD></TR><TR><TD>4</TD><TD>788</TD><TD>K4</TD><TD>56</TD><TD>25/06/15</TD><TD>22/03/15</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Expected answer</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>EXPECTED DATA</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Empid</TD><TD>JobId</TD><TD>DepId</TD><TD>Fin_Loc</TD><TD>Doj</TD><TD>updated Date</TD><TD>Start_Date</TD><TD>End_Date</TD></TR><TR><TD>1</TD><TD>312</TD><TD>B7</TD><TD>34</TD><TD>25/01/17</TD><TD>25/05/17</TD><TD>25/01/17</TD><TD>25/05/17</TD></TR><TR><TD>1</TD><TD>456</TD><TD>R3</TD><TD>55</TD><TD>25/01/17</TD><TD>25/06/17</TD><TD>26/05/17</TD><TD>25/06/17</TD></TR><TR><TD>1</TD><TD>212</TD><TD>I5</TD><TD>21</TD><TD>25/01/17</TD><TD>25/07/17</TD><TD>26/06/17</TD><TD>25/07/17</TD></TR><TR><TD>1</TD><TD>234</TD><TD>B4</TD><TD>65</TD><TD>25/01/17</TD><TD>25/06/18</TD><TD>26/07/17</TD><TD>25/06/18</TD></TR><TR><TD>2</TD><TD>245</TD><TD>N</TD><TD>78</TD><TD>25/06/16</TD><TD>25/06/18</TD><TD>25/06/16</TD><TD>25/06/18</TD></TR><TR><TD>3</TD><TD>343</TD><TD>N5</TD><TD>56</TD><TD>25/06/15</TD><TD>02/03/16</TD><TD>25/06/15</TD><TD>02/03/16</TD></TR><TR><TD>3</TD><TD>788</TD><TD>K4</TD><TD>56</TD><TD>25/06/15</TD><TD>22/03/17</TD><TD>03/03/16</TD><TD>22/03/17</TD></TR><TR><TD>3</TD><TD>567</TD><TD>T5</TD><TD>44</TD><TD>25/06/15</TD><TD>22/03/18</TD><TD>23/03/17</TD><TD>22/03/18</TD></TR><TR><TD>4</TD><TD>788</TD><TD>K4</TD><TD>56</TD><TD>25/06/15</TD><TD>22/03/15</TD><TD>25/06/15</TD><TD>22/03/15</TD></TR></TBODY></TABLE> Mon, 25 Jun 2018 18:03:53 GMT https://communities.sas.com/t5/SAS-Programming/how-to-split-Start-Date-and-End-Date-using-DOJ-and-Updated-date/m-p/473088#M121355 srinivaschary 2018-06-25T18:03:53Z Re: how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID https://communities.sas.com/t5/SAS-Programming/how-to-split-Start-Date-and-End-Date-using-DOJ-and-Updated-date/m-p/473092#M121357 <P>Explain how you derived the value of Start_Date for Empid=1.</P> Mon, 25 Jun 2018 18:11:15 GMT https://communities.sas.com/t5/SAS-Programming/how-to-split-Start-Date-and-End-Date-using-DOJ-and-Updated-date/m-p/473092#M121357 PaigeMiller 2018-06-25T18:11:15Z Re: how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID https://communities.sas.com/t5/SAS-Programming/how-to-split-Start-Date-and-End-Date-using-DOJ-and-Updated-date/m-p/473096#M121358 <P>Did you miss something to explain us?</P> <P>&nbsp;</P> <P>If you want to compare previous row values to current row values then try using LAG() function. eg: Start_Date=LAG(End_Date)+1</P> Mon, 25 Jun 2018 18:19:39 GMT https://communities.sas.com/t5/SAS-Programming/how-to-split-Start-Date-and-End-Date-using-DOJ-and-Updated-date/m-p/473096#M121358 SuryaKiran 2018-06-25T18:19:39Z Re: how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID https://communities.sas.com/t5/SAS-Programming/how-to-split-Start-Date-and-End-Date-using-DOJ-and-Updated-date/m-p/473235#M121412 <P>Sorry for late reply,</P><P>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.</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>SAMPLE DATA</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Empid</TD><TD>JobId</TD><TD>DepId</TD><TD>Fin_Loc</TD><TD>Doj</TD><TD>updated Date</TD></TR><TR><TD>1</TD><TD>212</TD><TD>I5</TD><TD>21</TD><TD>25/06/17</TD><TD>25/07/17</TD></TR><TR><TD>1</TD><TD>312</TD><TD>B7</TD><TD>34</TD><TD>25/06/17</TD><TD>25/05/17</TD></TR><TR><TD>1</TD><TD>456</TD><TD>R3</TD><TD>55</TD><TD>25/06/17</TD><TD>25/06/17</TD></TR><TR><TD>1</TD><TD>234</TD><TD>B4</TD><TD>65</TD><TD>25/06/17</TD><TD>25/06/18</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>EXPECTED DATA</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Empid</TD><TD>JobId</TD><TD>DepId</TD><TD>Fin_Loc</TD><TD>Start_Date</TD><TD>End_Date</TD></TR><TR><TD>1</TD><TD>312</TD><TD>B7</TD><TD>34</TD><TD>25/01/17</TD><TD>25/05/17</TD></TR><TR><TD>1</TD><TD>456</TD><TD>R3</TD><TD>55</TD><TD>26/05/17</TD><TD>25/06/17</TD></TR><TR><TD>1</TD><TD>212</TD><TD>I5</TD><TD>21</TD><TD>26/06/17</TD><TD>25/07/17</TD></TR><TR><TD>1</TD><TD>234</TD><TD>B4</TD><TD>65</TD><TD>26/07/17</TD><TD>25/06/18</TD></TR></TBODY></TABLE> Tue, 26 Jun 2018 04:45:20 GMT https://communities.sas.com/t5/SAS-Programming/how-to-split-Start-Date-and-End-Date-using-DOJ-and-Updated-date/m-p/473235#M121412 srinivaschary 2018-06-26T04:45:20Z Re: how to split Start_Date and End_Date using DOJ and Updated date group by EmployeeID https://communities.sas.com/t5/SAS-Programming/how-to-split-Start-Date-and-End-Date-using-DOJ-and-Updated-date/m-p/473253#M121419 <P>So for JobId 456, 212 and 234 start-date is after end-date ... a bit weird.</P> Tue, 26 Jun 2018 07:31:16 GMT https://communities.sas.com/t5/SAS-Programming/how-to-split-Start-Date-and-End-Date-using-DOJ-and-Updated-date/m-p/473253#M121419 andreas_lds 2018-06-26T07:31:16Z