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!
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
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;
Yes, and thanks and taht has been fixed to set. However I am still getting missing values in first forecast periods and onwards.
@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?
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.
@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;
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;
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.
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
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*******************************/
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
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).
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
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.