Hi, I am trying to calculate values based on prior values, either existing or calculated, using the lag operator. The initial calculations are based on lagged values of an existing field but latter calculations are based on lagged values of the initial calculations. So the argument of the lag operator must change. I have included the SAS code I have tried without much success. Also here is what I want to happen using Excel--I want to calculate the variable X with s, h, b, and p as given. My example has a lag of 2 but this may vary. Thanks. A B C D E 1 s h b p x 2 1 -3 100 0.12 3 1 -2 104 0.11 4 1 -1 102 0.15 5 1 0 110 0.13 6 1 1 105 0.10 112.727434 <-- =EXP(D6)*C4 7 1 2 110 0.15 127.801767 <-- =EXP(D7)*C5 8 1 3 100 0.10 124.583081 <-- =EXP(D8)*E6 9 2 -3 150 0.13 10 2 -2 165 0.14 11 2 -1 170 0.10 12 2 0 160 0.11 184.185882 13 2 1 155 0.15 197.511821 <-- =EXP(D13)*C11 14 2 2 145 0.10 176.827347 <-- =EXP(D14)*C12 15 2 3 150 0.12 222.693957 <-- =EXP(D15)*E13 /* Sample dataset */
data temp1;
input s h b p;
datalines;
1 -6 102 0.8
1 -5 110 0.12
1 -4 105 0.15
1 -3 100 0.12
1 -2 104 0.11
1 -1 102 0.15
1 0 110 0.13
1 1 105 0.10
1 2 110 0.15
1 3 100 0.10
1 4 105 0.11
1 5 105 0.15
1 6 110 0.13
2 -6 160 0.11
2 -5 155 0.15
2 -4 140 0.13
2 -3 150 0.13
2 -2 165 0.14
2 -1 170 0.10
2 0 160 0.11
2 1 155 0.15
2 2 145 0.10
2 3 150 0.12
2 4 160 0.13
2 5 170 0.13
2 6 155 0.14
;
run;
proc print data=temp1;
by s;
run;
/* Here use 2 period lag but lag can vary */
%let d=2;
data temp2;
set temp1;
by s h;
retain x2;
if h<=&d. then x1=lag&d.(b)*exp(p); if h<=0 then x1=.;
x2=coalesce(lag&d.(x1)*exp(p),lag&d.(x2)*exp(p)); if h<=&d. then x2=.;
x=coalesce(x2,x1);
run;
proc print data=temp2;
by s;
var s h b p x1 x2 x;
run;
... View more
