This program generates the variables LAPSED_12M, LAPSED_18M, LAPSED_24M, LAPSED36M to indicate whether the customer had at least one lapse of the corresponding length in months. (LAPSED_xxM='Y' or 'N'). For only those with a "Y", the corresponding target variable (TARGET_12M, ....) is generated according to the rules you mention.
Notes for this untested program:
I notice your date range goes up to 01nov2016, so I used 01nov2016 (as macrovar ENDDATE) the end of the last "gap" for all customers. (Say the customer buys something in early 2013, and nothing more. Then knowing the enddate of 01nov2016 tells me the customer has a 12M gap, 18M, 24M, and 36M gap, and will have 4 target variables=0).
Because the example sheet states that anyone with FIRST purchase xx or fewer months before the end_date cannot be said to have a lapse of size xx, they get a LAPSEC_xxM='N'. (i.e. they started too late to have the corresponding gap). To test for that condition there is an "if _n_=1 ..." do group that set corresponding cutdates for first purchase, which will be used against every first.customer_d record.
There is both a MERGE and a SET statement. The merge (without a by) allows easy access to the gap size of current record to next record (firstobs=2). The SET statement (with a by) is there just to establish first.customer_id and last.customer_id dummies.
Regards,
Mark
%let begdate=01nov2012;
%let enddate=01nov2016;
data want (drop=I next_date)
array lapse_length {4} _temporary_ (12,18,24,36);
array cutdate{4} _temporary_;
if _n_=1 then do I=1 to 4;
cutdate{I}=intnx('month',"&enddate"d,-1*lapse_length{i},'s');
end;
merge have
have (firstobs=2 keep=customerid date
rename=(customer=next_id date=next_date));
set have (keep=customer_id);
by customer_id;
array lapsed{4} $1 lapsed_12M lapsed_18M lapsed_24M lapsed_36M;
array target{4} target_12M target_18M target_24M target_36M;
retain lapsed: target: ;
if first.customer_id then do I=1 to 4;
if date>cutdate{I} then lapsed{I}='N';
else do;
lapsed{I}='Y';
target{I}=0; /* Start out assuming no subsequent purchase*/
end;
end;
if last.customer_id then next_date="&enddate"d;
monthsgap=intck('month',date,next_date,'continuous');
do I=1 to 4 ;
if lapsed{I}='Y' and monthsgap>lapse_length{I} and not(last.customer_id)
then target{I}=1;
end;
if last.customer_id;
run;
... View more
