I'm trying to keep measurement dates as a new index date if the measurement date is more than 180 days after the last saved index date (i.e., the last measurement date that met the >180 days condition). I'm having trouble iterating this condition every row by the ID group. I have this data: data have; input id measure_dt :mmddyy. measure_cnt; format measure_dt mmddyy10.; datalines; 1 1/22/2008 1 1 4/21/2008 2 1 8/4/2008 3 1 9/17/2008 4 2 5/2/2009 1 2 11/22/2009 2 2 1/9/2010 3 ; run; I want this: id measure_dt measure_cnt days since last index date new index date 1 1/22/2008 1 . 1/22/2008 1 4/21/2008 2 90 . 1 8/4/2008 3 195 8/4/2008 1 9/17/2008 4 44 . 2 5/2/2009 1 . 5/2/2009 2 11/22/2009 2 204 11/22/2009 2 1/9/2010 3 48 . I've tried the below code which is getting closer to what I want, but I think there are issues with my do loop as it's outputting multiple rows per each observation that I have. data want;
set have;
by id;
if first.id then do;
new_index_dt=measure_dt;
lag_index=measure_dt;
output;
end;
lag_index=lag(new_index_dt);
if first.id then lag_index=measure_dt;
do measure_cnt=2 to max(measure_cnt);
days_since_last=measure_dt-lag_index;
if days_since_last>=180 then new_index_dt=measure_dt;
else if days_since_last<180 then new_index_dt=lag_index;
lag_index=lag(new_index_dt);
output;
end;
format new_index_dt lag_index mmddyy10.;
run; I'm using SAS EG 8.3
... View more