Why do you run the first proc transpose, which reads DATA1 and writes out dataset WORK.WANT, only to immediately follow it with another proc transpose reading DATA2 and over-writing dataset WANT? Then why run the 2nd transpose, when you follow it with a DATA step that merges DATA1 and DATA2 to once again overwrite WANT? The first two proc's are completely superfluous.
Now if you are starting the ytd calculations from a data set sorted by metric, and date, with exactly 12 monthly records per year, then this program will set previous_ytd to missing for the first 12 records of each METRIC. I name the dataset as HAVE, from which you would produce WANT:
data want ;
set have;
by metric date;
previous_ytd=ifn(metric=lag12(metric),lag12(ytd),.);
run;
Now the "BY METRIC DATE" could be deleted, since the program code doesn't take advantage of it. But it does tell SAS to stop if the data is out of order. BTW, I assume that variable DATE is a true sas date value.
The IFN statement tests the current metric vs the lag12(metric). If they match (i.e. you are in the 13th and subsequent records), IFN returns previous_ytd=lag12(ytd). Otherwise it's set to missing.
But the advantage of the IFN statement is that it will ALWAYS execute the LAG12(ytd) function, even if the METRIC=lag12(METRIC) condition is false, and IFN ultimately returns the third argument (a missing value). In other words, the queue underneath the misleadingly-named LAG function is ALWAYS updated, even when IFN doesn't use it. This is a good way to address the problem described by @ballardw.
... View more