Is there a way of referencing the prior observation's values in SAS/SQL when defining a new var-CodePudding

I have a table of repeat customers, and I want to add a flag for each customer id if it's been more than 30 days since the previous purchase. It's arranged by ID and ascending date.

CustomerID        Date
001               02/15/2022
001               03/01/2022
002               01/19/2022
002               02/11/2022
002               03/30/2022
...

I would want to end up with

CustomerID        Date           Flag
001               02/15/2022     N
001               03/01/2022     N
002               01/19/2022     N
002               02/11/2022     N
002               03/30/2022     Y
...

The only way I can think of doing this is by referencing the previous row. Here's the pseudo-code to illustrate what I mean.

IF (CUSTOMERID = PRIOR.CUSTOMERID) AND (DATE - PRIOR.DATE >= 30) THEN FLAG = 'Yes'

However, after looking around some, I'm unsure how to accomplish this in SAS, either in a data step or proc sql. I assume there's some way of saving the value of a previous row, but I'm not familiar with that.

CodePudding user response：

That is what the LAG() function is for. The LAG() function returns the value saved from the last time you called it. So if you call it for every observation then the result is the value from the previous observation. Just make sure not to skip running the LAG() function on some observation or else the returned value will NOT be the previous observation's value.

data want;
  set have;
  by customerid date;
  if not first.customerid and (date-30 )> lag(date) then flag='YES';
  else flag='NO';
run;

Results

       Customer
Obs       ID             Date    flag

 1       001       2022-02-15    NO
 2       001       2022-03-01    NO
 3       002       2022-01-19    NO
 4       002       2022-02-11    NO
 5       002       2022-03-30    YES