For the bottom row (date 202212), why is the value of flag2=2? I ask, because it is apparently the month of a delivery, which I understand should be signified by flag2=1.
Assuming that is an error (which I have corrected below), this code produces what you want.
data have;
infile datalines dlm="|" dsd;
input
date :yymmn6.
flag1
flag2
id :$1.
flag : $30.
;
format date yymmn6.;
datalines;
202201|1|1|A|RECEIVED - OK|-|0
202202|1|2|A|N/RECEIVED - FIRST MONTH|202204|2
202203|2|2|A|N/RECEIVED - WAITING|-|-
202204|2|1|A|RECEIVED - LATE|-|-
202205|1|1|A|RECEIVED - OK|-|0
202206|1|2|A|N/RECEIVED - FIRST MONTH|202209|3
202207|2|2|A|N/RECEIVED - WAITING|-|-
202208|2|2|A|N/RECEIVED - WAITING|-|-
202209|2|1|A|RECEIVED - LATE|-|-
202210|1|1|A|RECEIVED - OK|-|0
202211|1|2|A|N/RECEIVED - FIRST MONTH|202212|1
202212|2|1|A|RECEIVED - LATE|-|-
;
data want (drop=_:);
do _n=1 by 1 until (flag2=1 or last.id);
set have;
by id date;
end;
if flag2=1 then _delivery_date=date; format _delivery_date yymmn6.;
do _n_=1 to _n;
set have;
if flag1=1 and flag2=1 then finish_date=.S; /*Same month delivery*/
else if flag1=2 and flag2=2 then finish_date=.W ; /*Waiting for delivery*/
else if flag1=1 and flag2=2 then finish_date=_delivery_date;
else if flag1=2 and flag2=1 then finish_date=.L; /*Received Late*/
if finish_date=.S then months_to_receipt=0;
else if not missing(finish_date) then months_to_receipt=intck('month',date,finish_date);
output;
call missing(months_to_receipt);
end;
format finish_date yymmn6. ;
run;
This code read each set of records up to a successful delivery (or end of the ID), in order to establish a delivery date. It then rereads (and outputs) the same set of records, generating the variables finish_date and months_to_receipt as needed, using the flag values and the established delivery date.
... View more