Have you considered working with a wide file instead of a long file? Using the sample data you provided and following along with your post as best as I could, I think the below code may work. It is very basic, using array processing for each year of data to look back on the prior year. Hope this helps! /*Create a wide file with a single row per ID.*/
proc sort data=have;
by id avg_in2016 avg_in2017 avg_in2018 baddatastart date;
run;
/*Since the average inches and bad data start are constant for each ID,*/
/*use those as by variables.*/
proc transpose data=have out=have_wide (drop=_name_) prefix=inches_;
by id avg_in2016 avg_in2017 avg_in2018 baddatastart;
var inches;
id date;
run;
/*Swap out zeroes for prior year, if required.*/
data have_wide_swap (drop=i);
set have_wide;
/*These could be made more dynamic depending on the volume of years you are working with*/
array yr1 {4} inches_201601-inches_201604;
array yr2 {4} inches_201701-inches_201704;
array yr3 {2} inches_201801-inches_201802;
/*Look at the year prior first. If not zero, use it.*/
/*Otherwise, use the year before that.*/
do i=1 to 2;
if yr2{i} ne 0 then yr3{i}=yr2{i};
else if yr2{i} eq 0 then yr3{i}=yr1{i};
end;
run;
... View more
