How to use lag function to calculate next observation in SAS

Question

Suppose the dataset has 3 columns

Obs  Theo  Cal  
1    20    20   
2    21    23   
3    21    .    
4    22    .    
5    21    .    
6    23    .    

Theo is the theoretical value while Cal is the estimated value.

I need to calculate the missing Cal. For each Obs, its Cal is a linear combination of previous two Cal values.

Cal(3) = Cal(2) * &coef1 + Cal(1) * &coef2.
Cal(4) = Cal(3) * &coef1 + Cal(2) * &coef2.

But Cal = lag1(Cal) * &coef1 + lag2(Cal) * &coef2 didn't work as I expected.

Answer 1

The problem with using lag is when you use lag1(Cal) you're not getting the last value of Cal that was written to the output dataset, you're getting the last value that was passed to the lag1 function. It would probably be easier to use a retain as follows:

data want(drop=Cal_l:);
    set have;
    retain Cal_l1 Cal_l2;

    if missing(Cal) then Cal = Cal_l1 * &coef1 + Cal_l2 * &coef2;

    Cal_l2 = Cal_l1;
    Cal_l1 = Cal;
run;