How to add months in macro variable?

[David_Billa]

I want to add 12 months in the macro variable which has a date value. I tried the code below but it's not producing the desired Output.

%let reporting_dt=20161231;
%let reporting_dt_new=intnx('month',input(&reporting_dt.,anydtdte7.),12,'E') ;
%put &reporting_dt_new.;

Excepted Output is, 20171231 for the macro variable "reporting_dt_new"

[PeterClemmensen]

Try this

%let reporting_dt = 20161231;
%let reporting_dt_new = %sysfunc(putn(%sysfunc(intnx(month, %sysfunc(inputn(&reporting_dt., yymmdd8.)), 12, e)), yymmddn8.));
%put &reporting_dt_new;

[Ksharp]

%let reporting_dt = 20161231;
%let reporting_dt_new =%sysfunc(intnx(month,%sysfunc(inputn(&reporting_dt., yymmdd8.)), 12, e), yymmddn8.);
%put &reporting_dt_new;

[Ksharp]

%let reporting_dt = 20161231;
%let reporting_dt_new =%sysfunc(intnx(month,%sysfunc(inputn(&reporting_dt., yymmdd8.)), 12, e), yymmddn8.);
%put &reporting_dt_new;

[Kurt_Bremser]

You need to internalize that the macro processor is a text engine, nothing more.

It will never do any kind of calculation/manipulation unless you force it to, by the use of macro functions.

Just look at the log:

 75         %put &reporting_dt_new.;
 intnx('month',input(20161231,anydtdte7.),12,'E')

The %LET will only assign text to the macro variable, no calculated values.

To force calculations, use functions like %SYSFUNC or %EVAL.

Further, if you need to use a value in code (as part of a calculation, or for comparisons), do not format it when storing into a macro variable. Otherwise, you will always have to convert it at the place where you use it. This is covered in Maxim 28.

[ballardw]

Please describe exactly what you intend to do with a macro variable with a content of 20171231.

You can't use that to filter or select actual date values  in any reasonable manner so I am curious.

[gamotte]

Hello,

As @Kurt_Bremser said, the macro language is not designed to manipulate data and perform calculations though

you can use some data step functions through %sysfunc.

As shown by @PeterClemmensen's answer, this can lead to rather complex expressions (3 %sysfunc calls) thus making debugging

more difficult.

Using data steps will provide more readable and reliable code.

If you need your value in the form of a macro variable, you still can export the result with a call symput (with maxim 28 in mind).

%let reporting_dt=20161231;

data _NULL_;
    reporting_dt=input("&reporting_dt",yymmdd8.);
    reporting_dt_new=intnx('month', reporting_dt,12,'E');

    call symputx("reporting_dt_new", reporting_dt_new);
    call symputx("reporting_dt_new_fmt", put(reporting_dt_new,yymmddn8.));
run;

%put &=reporting_dt_new;
%put &=reporting_dt_new_fmt;

[Astounding]

Is your starting always going to be the last day of the month?  Are you always looking to add exactly 12 months?  If so, you can do this simply:

%let reporting_dt_new = %eval(&reporting_dt + 10000);

The only complication you would run into is when the starting date is February 29, so the next year won't actually have a February 29.  (Depending on how you intend to use the new date this may or may not be a problem.)  At any rate, the more complete solution would be:

%if %substr(&reporting_dt, 5) = 0229 %then %let reporting_dt_new = %eval(&reporting_dt + 9999);
%else %let reporting_dt_new = %eval(&reporting_dt + 10000);

[David_Billa]

Thanks. What would be the value if I want to add only 3 months?

[Astounding]

Probably, the first couple of suggestions would be fine.  Just change 12 to 3.  When I say "probably", I'm noting that you haven't specified what the result should be if your starting point falls in the middle of the month:

20191215

[David_Billa]

It's not working if I try use it for comparison in where clause as shown below. Code which I used is,

Select * from &SYSLAST
where (intnx('month',input(GLOBAL_REPORTINGPERIOD ,anydtdte7.),0,'E') = &REPORTINGDATE | intnx('month',input(GLOBAL_REPORTINGPERIOD ,anydtdte7.),0,'E') = %eval(&REPORTINGDATE.+10000));
quit;

Value of the macro variable REPORTINGDATE is 20161231 and value of GLOBAL_REPORTINGPERIOD in &SYSLAST is '2017-12'.

Log:

359                 (intnx('month',input(GLOBAL_REPORTINGPERIOD ,anydtdte7.),0,'E') = &REPORTINGDATE |
359      ! intnx('month',input(GLOBAL_REPORTINGPERIOD ,anydtdte7.),0,'E') = %eval(&REPORTINGDATE.+10000))
SYMBOLGEN:  Macro variable REPORTINGDATE resolves to        20819
SYMBOLGEN:  Macro variable REPORTINGDATE resolves to        20819
360           ;
NOTE: Table RISK_CUSTOMER created, with 0 rows and 19 columns.

Appericiate if someone of you help me understand why I'm not getting any records in the output table. Any issues with the where clause?

[Kurt_Bremser · Accepted solution]

&reportingdate has the raw value, so just apply the INTNX function:

%let reportingdate=20819;

data test;
GLOBAL_REPORTINGPERIOD = "2017-12";
run;

proc sql;
Select * from test
where (
  intnx('month',input(GLOBAL_REPORTINGPERIOD!!"-01",yymmdd10.),0,'E') = &REPORTINGDATE or
  intnx('month',input(GLOBAL_REPORTINGPERIOD!!"-01",yymmdd10.),0,'E') = intnx('year',&REPORTINGDATE.,1,"e")
  )
;
quit;

I also dislike the ANY type of informats, I rather force the issue.

[Kurt_Bremser]

As you can see from these log lines:

SYMBOLGEN:  Macro variable REPORTINGDATE resolves to        20819
SYMBOLGEN:  Macro variable REPORTINGDATE resolves to        20819

the value in &REPORTINGDATE is not 20161231.

[ballardw]

---

@David_Billa wrote:

It's not working if I try use it for comparison in where clause as shown below. Code which I used is,

Select * from &SYSLAST
where (intnx('month',input(GLOBAL_REPORTINGPERIOD ,anydtdte7.),0,'E') = &REPORTINGDATE | intnx('month',input(GLOBAL_REPORTINGPERIOD ,anydtdte7.),0,'E') = %eval(&REPORTINGDATE.+10000));
quit;

Value of the macro variable REPORTINGDATE is 20161231 and value of GLOBAL_REPORTINGPERIOD in &SYSLAST is '2017-12'.

Log:

359                 (intnx('month',input(GLOBAL_REPORTINGPERIOD ,anydtdte7.),0,'E') = &REPORTINGDATE |
359      ! intnx('month',input(GLOBAL_REPORTINGPERIOD ,anydtdte7.),0,'E') = %eval(&REPORTINGDATE.+10000))
SYMBOLGEN:  Macro variable REPORTINGDATE resolves to        20819
SYMBOLGEN:  Macro variable REPORTINGDATE resolves to        20819
360           ;
NOTE: Table RISK_CUSTOMER created, with 0 rows and 19 columns.

Appericiate if someone of you help me understand why I'm not getting any records in the output table. Any issues with the where clause?

---

Really garbage for dealing with dates. And did you read the question I posted earlier about the values you generate can't be used for comparing with actual date values???

20161231 is not a date value. The numeric value to use would be 20819 and then use the INTNX function to advance the value one year, NOT date+10000, if you are going to compare to a date such as results from INPUT with a date informat.

And there is no reason to use %eval in the middle of SQL or Data step calculations in that fashion. You'll get the same result with (&var. + 10000)

I suspect you would likely save yourself a bunch of other headaches by having a version of GLOBAL_REPORTINGPERIOD as a date instead of a character value. You can always assign a format to make the date look like that YYYY-MM string when needed and you don't end up writing