LAG Function for grouping dates

[ColleenCB]

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.

[Kurt_Bremser]

Please supply your data in usable form (data step with datalines, posted in a code box).

Excel files tell us nothing about SAS variable types and attributes.

[ballardw]

LAG does not generally work in SAS Proc SQL, so you need to show the entire procedure code you are attempting so we have a chance of understanding what you are trying to accomplish.

If this is pass through to another DBMS we need that information as well.

Since "Lag" is intended to look at a previous record then the first record does not have a previous so should return missing.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.