Using macro variable within function

[hatsumi]

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;

Jul-12 67 4 0 1

Aug-12 33 65 1 1

Sep-12 91 15 0 1

Oct-12 30 35 0 1

Nov-12 52 45 0 0

Dec-12 100 89 0 1

Jan-13 89 20 1 1

Feb-13 19 8 0 0

Mar-13 8 10 1 1

Apr-13 73 57 0 0

May-13 38 76 1 0

Jun-13 33 55 1 1

Jul-13 67 4 0 1

Aug-13 33 65 1 1

Sep-13 91 15 0 1

Oct-13 30 35 0 1

Nov-13 52 45 0 0

Dec-13 100 89 0 1

Jan-14 39 39 1 1

Feb-14 100 92 1 0

Mar-14 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

[Reeza]

month(today())+1

In December that becomes 13 so it will fail

Why do you have the +1?

[hatsumi]

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;

[Reeza]

I think you want the intck function not the intnx function.

[Reeza · Accepted solution]

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;

[hatsumi]

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

[Reeza]

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.));

[hatsumi]

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!!

[ballardw]

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.

[hatsumi]

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 22-322: 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

[ballardw]

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.

[hatsumi]

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.

[slchen]

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

[hatsumi]

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.

• A=1month after the enddate
• B=1year after the enddate

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;

.

[slchen]

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