Your problem description is not sufficient to give a robust answer. In particular, are you creating a new dataset each week from two data sources (the year-to-date data and the new single-week data)? This would make it easy to determine what the maximum week number should be for each state. On the other hand, if the data are already in a single data set, per your sample, then a bit more work is require to make a program that determination of the miximum valid week number.
I'm going to assume you have two datasets: YTD (the historical data up to now) and SINGLE_WEEK (the most recent week of rainfall data)
Then this should work. It's untested, because we need sample data in the form of working data steps:
data default_rainfall (drop=_:) / view=default_rainfall ;
if _n_=1 then set single_week (keep=week rename=(week=_most_recent_week));
/* Now read one obs per state */
set ytd (keep=state);
by state;
retain rainfall 0;
if first.state then do week=1 to _most_recent_week;
output;
end;
run;
data want;
merge default_rainfall ytd single_week;
by state week;
run;
This program first creates a complete dummy history called default_rainfall, including the most recent week, but with all rainfall values set to zero.
Then a simple SAS merge statement does everything you need. But note that default_rainfall must be the first names dataset in the merge statement. That's because merge will overwrite the rainfall values from default_rainfall by values from the other two datasets, whenever there is a match of state and week. It doesn't matter what order you put the other two datasets in, assuming they current history and the new single week never have the same week present.
