topic Using macro variable within function in SAS Programming

Using macro variable within function

hatsumi — Tue, 27 May 2014 15:46:50 GMT

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

Re: Using macro variable within function

Reeza — Tue, 27 May 2014 16:14:07 GMT

month(today())+1

In December that becomes 13 so it will fail

Why do you have the +1?

Re: Using macro variable within function

ballardw — Tue, 27 May 2014 17:06:13 GMT

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.

Re: Using macro variable within function

slchen — Tue, 27 May 2014 17:28:17 GMT

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

Re: Using macro variable within function

hatsumi — Tue, 27 May 2014 18:32:45 GMT

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

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

Re: Using macro variable within function

hatsumi — Tue, 27 May 2014 18:34:01 GMT

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.

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;

Re: Using macro variable within function

hatsumi — Tue, 27 May 2014 18:37:44 GMT

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;

Re: Using macro variable within function

Reeza — Tue, 27 May 2014 19:01:34 GMT

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

Re: Using macro variable within function

ballardw — Tue, 27 May 2014 19:12:20 GMT

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.

Re: Using macro variable within function

slchen — Tue, 27 May 2014 19:28:16 GMT

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

Re: Using macro variable within function

Reeza — Tue, 27 May 2014 19:57:16 GMT

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;

Re: Using macro variable within function

hatsumi — Wed, 28 May 2014 14:05:06 GMT

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.

Re: Using macro variable within function

hatsumi — Wed, 28 May 2014 14:08:01 GMT

Hi Slchen, you are right, I should have not included 'b' in my intnx function. Thank you for pointing that out! It was a big hint that you gave me to use intnx function to increment month to calculate the period I wanted.

Re: Using macro variable within function

hatsumi — Wed, 28 May 2014 14:31:41 GMT

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

Re: Using macro variable within function

Reeza — Wed, 28 May 2014 14:40:14 GMT

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

Re: Using macro variable within function

hatsumi — Wed, 28 May 2014 14:52:18 GMT

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