Good day everyone, Hope you are well. Please can you assist with a LAG"ging" problem... This is what I want to achieve excluding the DatecreatedID - the last 2 columns (this data is attached) MonthKey EmployeeKey Staff_Indi MonthsOfService EngagementDate DatecreatedID DateFrom DateTo 201003 1234567 Yes 56 20050701 20100401 2005/07/01 2010/04/01 201011 1234567 Yes 64 20050701 20101103 2010/04/02 2010/11/03 201405 1234567 Yes 106 20050701 20140501 2010/11/04 2014/05/01 201611 1234567 Yes 136 20050701 20161101 2014/05/02 2016/11/01 201702 1234567 Yes 139 20050701 20170201 2016/11/02 2017/02/01 201709 1234567 Yes 146 20050701 20170902 2017/02/02 2017/09/02 In SQL we have the following code to retrieve this data and my problem is the last few lines of code that have been bolded is a LAG Function which I cannot convert to SAS coding. The last 2 columns are defrived from the DatecreatedID - the date that something changed on EEs profile. This is a varchar which is converted into a date. When this code is run, the first DateFrom pops up as Null for the first employee, but for the 2nd it is correct again pulling the engagementdate select fc.MonthKey , '1234567' as EmployeeKey , Case When fc.Headcount = 1 then 'Yes' When fc.Headcount = 0 then 'No' Else 'Unknown' end as 'Staff_Indi' , fc.MonthsOfService /* PRE CALCULATED MONTHS OF SERVICE */ , fc.CompanyEngagementDateID as 'EngagementDate' /* DATE EMPLOYEE STARTED WITH THE FIRM */ , fcec.DatecreatedID /* DATE THE EMPLOYEE PROFILE CHANGED */ , Case When (DateAdd(day,1,convert(date,convert(varchar(8),(LAG(fcec.DatecreatedID) over (Order By e.employeekey, fcec.DatecreatedID))))) >= convert(date,convert(varchar(8),fc.DatecreatedID ))) Then convert(date,convert(varchar(8),fc.EngagementDateID )) Else DateAdd(day,1,convert(date,convert(varchar(8),(LAG(fcec.DatecreatedID) over (Order By e.employeekey, fcec.DatecreatedID))))) End as DateFrom , convert(date,convert(varchar(8),fcec.DatecreatedID )) as DateTo Will appreciate assistance with this. Thanks & have a great day.
... View more