BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hatsumi
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

15 REPLIES 15
Reeza
Super User

month(today())+1


In December that becomes 13 so it will fail Smiley Sad

Why do you have the +1?

hatsumi
Obsidian | Level 7

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
Super User

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

Reeza
Super User

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
Obsidian | Level 7

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
Super User

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 Smiley Sad

Since you seem to be operating at a monthly basis perhaps consider using Jan2014 instead?

call symput('forecastdate', put(forecastdate, monyy7.));

hatsumi
Obsidian | Level 7

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

ballardw
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 2850 views
  • 6 likes
  • 4 in conversation