BookmarkSubscribeRSS Feed
srinivaschary
Calcite | Level 5

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
4 REPLIES 4
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
SuryaKiran
Meteorite | Level 14

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
srinivaschary
Calcite | Level 5

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
andreas_lds
Jade | Level 19

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

sas-innovate-2024.png

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.

 

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
  • 4 replies
  • 711 views
  • 0 likes
  • 4 in conversation