Run the following and see if it can be adapted to meet your needs. One difference from your example is that if there is not data for the day prior, this delivers 0, not missing. One could probably address that, but I figured I'd see if this comes close first. data have;
do date = '15DEC2016'd to '10JAN2017'd;
amt = 150 + round(rannor(8) * 30);
output;
end;
run;
%macro test;
proc sql noprint;
SELECT date INTO :dt1 - :dt99 FROM have;
%let cnt = &sqlObs;
quit;
%do i = 1 %to &cnt;
%let dayBefore = %eval(&&dt&i - 1);
proc sql;
SELECT DISTINCT max(date) AS date format date9.,
max(amt * (date = &&dt&i)) AS one,
max(amt * (date = &dayBefore)) AS two
FROM have
WHERE date in (&&dt&i, &dayBefore);
quit;
%end;
%mend test;
%test
... View more
