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.
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