Hi,
I have below program that works, but I would like to make a slight modification.
Currently, the program counts the number of months between next month and a year after from this month and takes this value as 'niter' to do a regressions in a loop.
Modifications I would like to make are:
1. Instead of counting months based on today() function, I would like to use macro variable defined as "&enddate". (Whenever I include "&enddate" instead of "today()", SAS produces an error.)
2. Instead of showing Horizon&n as a regression title, I want to show the forecasting date which is calculated by forecastdate=intnx('month',&enddate,&n)
(I tried to define a macro variable "forecastdate" and refer to it as a regression title, but it always results in error because "&n" cannot be read by SAS although it works in the other sections of the code.)
I was reading macro section of SAS textbook and trying different ways, but it seems I cannot figure this out myself.. I appreciate if someone could help me with the code. Thanks!
%let enddate='01Dec2013'd;
/*Read the dataset in SAS*/
data test;
format date yymmd.;
infile datalines missover;
input D$ Price Volume A B;
date = input(cats("01", D), date9.);
drop D;
datalines;
Jul12 67 4 0 1
Aug12 33 65 1 1
Sep12 91 15 0 1
Oct12 30 35 0 1
Nov12 52 45 0 0
Dec12 100 89 0 1
Jan13 89 20 1 1
Feb13 19 8 0 0
Mar13 8 10 1 1
Apr13 73 57 0 0
May13 38 76 1 0
Jun13 33 55 1 1
Jul13 67 4 0 1
Aug13 33 65 1 1
Sep13 91 15 0 1
Oct13 30 35 0 1
Nov13 52 45 0 0
Dec13 100 89 0 1
Jan14 39 39 1 1
Feb14 100 92 1 0
Mar14 86 46 1 0
;
/*Determine the number of iterations*/
/*Create lagged variables and run regressions in a loop*/
%macro regression;
data _null_;
call symputx('niter',intck('month', mdy(month(today())+1,01,year(today())), mdy(12,01,year(today())+1)));
run;
%do n=1 %to &niter;
data data_horizon&n;
set test;
A_L=lag&n(A);
B_L=lag&n(B);
proc reg data=data_horizon&n outest=Reg_Horizon&n TABLEOUT;
Horizon&n: model Price=A B A_L B_L/NOPRINT ADJRSQ RSQUARE;
Horizon&n: model Volume=A B A_L B_L/NOPRINT ADJRSQ RSQUARE;
%end;
run;
%mend;
%regression
/*Consolidate all the regression outputs into a single dataset*/
%macro combine;
Data Regression_output;
set
%do i = 1 %to &niter;
Reg_horizon&i
%end;
;
run;
%mend;
%combine
Your code seemed fairly close to me.
Try the following changes, use intnx to increment the month by 1, instead of +1.
Create forecast date macro variable.
Append results in one step.
Let me know how it works.
%macro regression;
data _null_;
call symputx('niter',intck('month', intnx('month', mdy(month(&enddate),01,year(&enddate)), 1, 'b'), mdy(12,01,year(&enddate)+1)));
run;
%do n=1 %to &niter;
data data_horizon&n;
set test;
A_L=lag&n(A);
B_L=lag&n(B);
run;
data _null_;
forecastdate=intnx('month',&enddate,&n)
call symput(forecastdate, put(forecastdate, date9.));
run;
title "Regression model at &forecastdate";
proc reg data=data_horizon&n outest=Reg_Horizon&n TABLEOUT;
Horizon&n: model Price=A B A_L B_L/NOPRINT ADJRSQ RSQUARE;
Horizon&n: model Volume=A B A_L B_L/NOPRINT ADJRSQ RSQUARE;
*Append results to master table;
proc append base=regression_output data=data_horizon&n;
run;
%end;
run;
%mend;
month(today())+1
In December that becomes 13 so it will fail
Why do you have the +1?
Hi Reeza, you are exactly right! Thanks for pointing that out!
With a help from slchen, here is a revised code below, but I am not getting the result I want.
Do you see what I did wrong here? When I ran this, SAS returns the value of “&niter” as “620061”... but if enddate is 01Dec2013, A is 01Jan2014 and B is 01Dec2014 so there are only 12months and thus “&niter’ should return “12” instead of “620061”.
%let enddate="01Dec2013"d;
data _null_;
call symputx('niter',intnx('month',intnx('month',%unquote(&enddate),1,'b'),intnx('year',%unquote(&enddate),1,'b')));
run;
%put &niter;
I think you want the intck function not the intnx function.
Your code seemed fairly close to me.
Try the following changes, use intnx to increment the month by 1, instead of +1.
Create forecast date macro variable.
Append results in one step.
Let me know how it works.
%macro regression;
data _null_;
call symputx('niter',intck('month', intnx('month', mdy(month(&enddate),01,year(&enddate)), 1, 'b'), mdy(12,01,year(&enddate)+1)));
run;
%do n=1 %to &niter;
data data_horizon&n;
set test;
A_L=lag&n(A);
B_L=lag&n(B);
run;
data _null_;
forecastdate=intnx('month',&enddate,&n)
call symput(forecastdate, put(forecastdate, date9.));
run;
title "Regression model at &forecastdate";
proc reg data=data_horizon&n outest=Reg_Horizon&n TABLEOUT;
Horizon&n: model Price=A B A_L B_L/NOPRINT ADJRSQ RSQUARE;
Horizon&n: model Volume=A B A_L B_L/NOPRINT ADJRSQ RSQUARE;
*Append results to master table;
proc append base=regression_output data=data_horizon&n;
run;
%end;
run;
%mend;
Hi Reeza, thank you for your help! I liked your proc append instead of my code and "niter" returns the exact value I want!
For &forecastdate titles, I want to place them like below as I am only storing the output to SAS dataset. (not printing the regression output)
When I run this code, it seems like that SAS does not accept &forecastdate in below location...
I was trying to debug, but &forecastdate resolves to the correct date (01Jan2014 for niter=1) as per below log and I'm quite not sure why SAS is not accepting "01Jan2014".
Do you have any idea?
data _null_;
forecastdate=intnx('month',&enddate,&n);
call symput('forecastdate', put(forecastdate, date9.));
run;
proc reg data=data_horizon&n outest=Reg_Horizon&n TABLEOUT;
&forecastdate: model Price=A B A_L B_L/NOPRINT ADJRSQ RSQUARE;
&forecastdate: model Volume=A B A_L B_L/NOPRINT ADJRSQ RSQUARE;
Partial log from SAS:
SYMBOLGEN: Macro variable FORECASTDATE resolves to 01JAN2014
NOTE: Line generated by the macro variable "FORECASTDATE".
1 01JAN2014

180
MPRINT(REGRESSION): 01JAN2014: model Volume=A B A_L B_L/NOPRINT ADJRSQ RSQUARE;
NOTE: The previous statement has been deleted.
WARNING: The data set WORK.REG_HORIZON1 may be incomplete. When this step was stopped there were
0 observations and 0 variables.
WARNING: Data set WORK.REG_HORIZON1 was not replaced because this step was stopped.
NOTE: PROCEDURE REG used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
I'm not 100% sure, but my log seems to indicate that label has to be valid SAS name, so 32 chars and starts with a character not a number
Since you seem to be operating at a monthly basis perhaps consider using Jan2014 instead?
call symput('forecastdate', put(forecastdate, monyy7.));
Reeza, thanks for your quick response! You are absolutely right! Although I thought the forecastdate has been converted to character by put function.. but the code finally works and I'm so happy!! Thank you very much for your help!!!
Ballardw and Slchen, I also want to thank you for your advice!!
It would help to show the code you used with the &endate variable and how you tried to create your forecast date macro variable.
data _null_;
forecastdate = intnx('month',&enddate,&n);
call symputx ('Forecastdate',put(forecastdate,date9.));
run;
Should get a useable value of &Forcastdate for titles. Put the data step just before the title statement or proc with title.
Hi ballardw, thanks for your response! My code was very similar to yours. I ran your code, but I got the exact same error again and somehow “&n” is not recognized well by SAS and this was the exact issue I had with my code. I’m not sure why though.. because “&n” in other parts of the code just works fine.
398 %let enddate="01Dec2013"d;
399 data _null_;
400 forecastdate = intnx('month',&enddate,&n);

22
WARNING: Apparent symbolic reference N not resolved.
ERROR 22322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, INPUT, PUT.
401 call symputx ('Forecastdate',put(forecastdate,date9.));
402 run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
Did you have a value, such as in the loop or a %let statment to test the code? This would have to be within the loop defining &n,
%do n=1 %to &niter;
or for a basic syntax test:
%let enddate="01Dec2013"d;
%let n = 3;
data _null_;
forecastdate = intnx('month',&enddate,&n);
call symputx ('ForeCastDate', put(forecastdate,date9.));
run;
%put &forecastdate.
If this fails you may need to contact Tech Support.
Hi ballardw, I did not know "&n" works only within loop function. That's very useful to know. Thanks! but I wanted "&n" to work without defining %let n = 3. So I'm still testing codes. Your codes works perfectly if I define n as 3.
Try like this:
%let enddate="01Dec2013"d;
%macro regression;
data _null_;
call symputx('niter',intck('month',%unquote(&enddate), intnx('month',today(),0,'b')));
run;
%put &niter;
%do n=1 %to &niter;
%let month=%sysfunc(putn(%sysfunc(intnx(month,%unquote(&enddate),&n)),date9.));
%put &month;
%end;
%mend;
%regression
Hi slchen, thanks for your response! Your first code runs smoothly, but I changed your formula slightly so that I get the number of months between A and B below.
So I ran below modified version of your code below, but now SAS returns the value of “&niter” as “620061”... but if enddate is 01Dec2013, A is 01Jan2014 and B is 01Dec2014 so there are only 12months and thus “&niter’ should return “12” instead of “620061”. Do you see what I did wrong?
%let enddate="01Dec2013"d;
data _null_;
call symputx('niter',intnx('month',intnx('month',%unquote(&enddate),1,'b'),intnx('year',%unquote(&enddate),1,'b')));
run;
%put &niter;
For your second code, “&n” seems to be not recognized by SAS and this is the issue I encountered previously as well. For some reasons, “&n” does not work within a function… although it is well recognized in the other parts of codes.
Below is the error message I got FYI:
438 %let month=%sysfunc(putn(%sysfunc(intnx(month,%unquote(&enddate),&n)),date9.));
WARNING: Apparent symbolic reference N not resolved.
WARNING: Apparent symbolic reference N not resolved.
ERROR: Argument 3 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not
a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution
of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
439 %put &month;
.
No problem when ran this code. In addition, I don't understand why you used: intnx('year',%unquote(&enddate),1,'b')), there is no 'begin' of year in intnx function.
1 %let enddate="01Dec2013"d;
2
3 %macro regression;
4 data _null_;
5 call symputx('niter',intck('month',%unquote(&enddate),
5 ! intnx('month',today(),0,'b')));
6 call symputx('new',
6 ! put(intnx('year',%unquote(&enddate),1),year4.));
7 run;
8 %put &niter &new;
9
10 %do n=1 %to &niter;
11 %let
11 ! month=%sysfunc(putn(%sysfunc(intnx(month,%unquote(&enddate),&n)),date9
11 ! .));
12 %put &month;
13 %end;
14 %mend;
15
16
17
18 %regression
NOTE: DATA statement used (Total process time):
real time 0.77 seconds
cpu time 0.01 seconds
5 2014
01JAN2014
01FEB2014
01MAR2014
01APR2014
01MAY2014
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.