LAG Function for grouping dates

ColleenCB — Tue, 30 Mar 2021 08:44:43 GMT

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.

Re: LAG Function for grouping dates

Kurt_Bremser — Tue, 30 Mar 2021 08:50:09 GMT

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.

Re: LAG Function for grouping dates

ballardw — Tue, 30 Mar 2021 14:24:16 GMT

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.

topic LAG Function for grouping dates in SAS Programming

LAG Function for grouping dates

Re: LAG Function for grouping dates

Re: LAG Function for grouping dates