circular reference problem in SAS

[ubshams]

Hi Everyone,

   I am trying to create some forecasts (has to be calculated mathematically) in SAS. The sample data and forecasts' nature is shown attached. I can do it in Excel, but no able to do it in SAS. I am using the "Retain" statement in SAS but that doesn't seem to work for me. I get missing values in the forecast periods. 

Here, month 0 is the last period of "actuals" and forecasts begin from month 1.

data want;
set have;
retain variable2;
if month<=1 then do;
variable2 = lag1(variable1);
variable1_f=variable1;
end;
else do;

variable4 = variable3*variable2;
variable6 = variable5*variable2;
variable1_f = variable2+variable6-variable4
end;
run;

Excel output is attached (which works).

How can this be implemented in SAS? Thanks in advance!

[Kurt_Bremser]

This

data want;
data have;
retain variable2;

creates an empty dataset "want", and then creates a dataset "have" without using any input data. I strongly guess you wanted

data want;
set have;
retain variable2;

[ubshams]

Yes, and thanks and taht has been fixed to set. However I am still getting missing values in first forecast periods and onwards.

[ballardw]

---

@ubshams wrote:

Yes, and thanks and taht has been fixed to set. However I am still getting missing values in first forecast periods and onwards.

---

You have to rebuild your previous version of "have" from the beginning or a previous step as you have overwritten the one you had. Has that been done?

[ubshams]

Yes and i just re-ran everything and all pervious data steps.

Still same issue (missing values).Variable2 (which is lag of variable1) gets created untill the first month. Then nothing. 

The other variables are just missing values. 

[ballardw]

---

@ubshams wrote:

Yes and i just re-ran everything and all pervious data steps.

Still same issue (missing values).Variable2 (which is lag of variable1) gets created untill the first month. Then nothing. 

The other variables are just missing values. 

---

LAG is a queue function and when use the Lag function after an if statement it tends to do things in a manner that takes a lot of time to figure out, basically the "last" value used by the lag is the previous record that the If was true, not the immediately previous record you may expect.

If you always want to have the previous record value modify your code so that lag is executed outside of the if. And that likely means you don't always want that value in the output so drop it.

data want;
   set have;
   retain variable2;
   lv1 = lag(variable1);
   if month<=1 then do;
      variable2 = lv1;
      variable1_f=variable1;
   end;
   else do;

      variable4 = variable3*variable2;
      variable6 = variable5*variable2;
      variable1_f = variable2+variable6-variable4 ;
   end;
   drop lv1;
run;

[yabwon]

Hi @ubshams ,

Maybe I'm missing something, but shouldn't the Lag() be used in both parts of IF/THEN/ELSE?

all the best

Bart

look at the code here:

data have;
input month variable1 variable2 variable3 variable5;
cards;
-2 501       500 0.10 0.05
-1 502         . 0.09 0.05
 0 510         . 0.08 0.05
 1 499.8       . 0.07 0.05
 2 484.806     . 0.08 0.05
 3 465.41376   . 0.09 0.05
 4    .        . 0.10 0.05
 5    .        . 0.10 0.05
 6    .        . 0.06 0.05
;
run;

data want;
  set have;
  retain variable2 500;

  lag1_variable1 = lag1(variable1);

  if month<=1 then 
    do;
      variable2 = variable2 <> lag1_variable1;
      variable1_f=variable1;
    end;
  else 
    do;  
      variable4 = variable3*lag1_variable1;
      variable6 = variable5*lag1_variable1;
      variable1_f = lag1_variable1 + variable6-variable4 ;
    end;
run;

[ubshams]

Thanks Yabwon. 

I am not sure. i didn't know you could use a statement like that with equal sign and <> together.

The problem i am having is that how do i calculate the forecasts for all variables (variable1, variable2, variable4 and variable 6 all the way to month 6?

I can do it in excel one by one, but SAS just stops and gives me missing values. Variable 3 and Variable5 forecasts are already given. 

[yabwon]

Hi @ubshams ,

could you modify my input data code to look like your starting point (which values are give)? and confirm rules you are using for calculating prediction? we will be on the same pare and it will be easier to figure it out 

All the best

Bart

[ubshams]

Ok, thanks!  Here you go updated data input and formulas for predictions:

data have;

input month variable1 variable2 variable3 variable4 variable5 variable6;

cards;

-2 501 500 0.10 100 0.05 601

-1 502 501 0.09 45.09 0.05 25.05

0 510 502 0.08 40.16 0.05 25.1

1 . . 0.07 . 0.05 .

2 . . 0.08 . 0.05 .

3 . . 0.09 . 0.05 .

4 . . 0.10 . 0.05 .

5 . . 0.10 . 0.05 .

6 . . 0.06 . 0.06 .

;

run;

/*****formulas to produce predictions through period 6 (end of dataset)*****/

*variable2 = lag(variable1);

*variable4 = variable3*variable2;

*variable6 = variable5 * variable2;

*variable1 = variable2 + variable6 - variable4;

/*****End of formulas to produce predictions*******************************/

[yabwon]

Hi @ubshams,

the following one:

data want;
  set have;
  retain var1lag; drop var1lag;
   
  if month > 0 then
    do;
      variable2 = var1lag;
      variable4 = variable3 * variable2;
      variable6 = variable5 * variable2;
      variable1 = variable2 + variable6 - variable4;

      var1lag = variable1  ;
    end;
  else var1lag = lag(variable1);   
run;
proc print data = want;
run;

gave:

Obs    month    variable1    variable2    variable3    variable4    variable5    variable6

 1       -2      501.000      500.000        0.10       100.000        0.05       601.000
 2       -1      502.000      501.000        0.09        45.090        0.05        25.050
 3        0      510.000      502.000        0.08        40.160        0.05        25.100
 4        1      491.960      502.000        0.07        35.140        0.05        25.100
 5        2      477.201      491.960        0.08        39.357        0.05        24.598
 6        3      458.113      477.201        0.09        42.948        0.05        23.860
 7        4      435.207      458.113        0.10        45.811        0.05        22.906
 8        5      413.447      435.207        0.10        43.521        0.05        21.760
 9        6      413.447      413.447        0.06        24.807        0.06        24.807

is it the expected output?

All the best

Bart

[ubshams]

Almost, in Month 1 variable2 needs to equal 510 (which is the previous month's value for Variable1). But now Variable2 is 502 (which is previous period value of variable2).

[yabwon · Accepted solution]

This one seems to be doing it:

data want;
  set have;
  retain var1lag; drop var1lag;
   
  if month > 0 then
    do;
      variable2 = var1lag;
      variable4 = variable3 * variable2;
      variable6 = variable5 * variable2;
      variable1 = variable2 + variable6 - variable4;   
    end;
 var1lag = variable1; 
 
run;
proc print data = want;
run;

Bart

[ubshams]

Thanks Bart, it works now.

On a related matter. If I want to create a new variable, let's say variable7, which, in each of the predicted time periods,  just multiplies "the month 0" value of variable5" with the respective value of variable2, is there a way to code that up? Right now I am just hard coding that number (which is not that efficient).

For e.g. I want variable7 in time 1 to be Variable2 value at month1 multiplied by Variable5 value in month 0. Then I want Variable7 in time 2 to be Variable5 value in month 0 to be multiplied by variable2 value in month 2. ...and so on.

[yabwon]

Do you mean something like that:

data want;
  set have;
  retain var1lag Var5_at_0; drop var1lag Var5_at_0;
   
  if month > 0 then
    do;
      variable2 = var1lag;
      variable4 = variable3 * variable2;
      variable6 = variable5 * variable2;
      variable1 = variable2 + variable6 - variable4;  
      
      variable7 =   variable2 * Var5_at_0;
    end;
 var1lag = variable1; 

  if month = 0  then Var5_at_0 = variable5;
 
run;
proc print data = want;
run;

?

Bart