Ok, here's my shot at it:
data want;
set work.kpi_data;
retain prev_flag_kpi prev_diff;
by SubjectId notsorted;
if first.subjectId then do;
prev_flag_kpi = .;
prev_diff = .;
end;
else do;
if prev_flag_kpi = 1 and flag_KPI then do;
if (-21 <= prev_diff <= 0 and 0 <= differences_in_days <=7) or
(-21 <= differences_in_days <= 0 and 0 <= prev_diff <=7)
then Flag_Consecutive = 1;
end;
end;
if not last.SubjectId then do;
prev_flag_kpi = flag_KPI;
prev_diff = differences_in_days;
end;
run;
I'm noting the data is not sorted on SubjectId. As a data person I'm missing a clear unique key for each row, like a sequence no, or date.
... View more
