topic Re: How to add months in macro variable? in SAS Programming

How to add months in macro variable?

David_Billa — Wed, 03 Feb 2021 09:42:07 GMT

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"

Re: How to add months in macro variable?

PeterClemmensen — Wed, 03 Feb 2021 09:56:55 GMT

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;

Re: How to add months in macro variable?

Kurt_Bremser — Wed, 03 Feb 2021 10:41:28 GMT

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.

Re: How to add months in macro variable?

ballardw — Wed, 03 Feb 2021 10:43:02 GMT

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.

Re: How to add months in macro variable?

gamotte — Wed, 03 Feb 2021 11:08:29 GMT

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;

Re: How to add months in macro variable?

Ksharp — Wed, 03 Feb 2021 11:15:50 GMT

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

Re: How to add months in macro variable?

Ksharp — Wed, 03 Feb 2021 11:22:40 GMT

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

Re: How to add months in macro variable?

Astounding — Wed, 03 Feb 2021 13:09:58 GMT

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

Re: How to add months in macro variable?

David_Billa — Wed, 03 Feb 2021 13:19:13 GMT

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

Re: How to add months in macro variable?

Astounding — Wed, 03 Feb 2021 13:26:38 GMT

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

Re: How to add months in macro variable?

David_Billa — Wed, 03 Feb 2021 14:21:11 GMT

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?

Re: How to add months in macro variable?

Kurt_Bremser — Wed, 03 Feb 2021 14:46:09 GMT

&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.

Re: How to add months in macro variable?

Kurt_Bremser — Wed, 03 Feb 2021 14:48:43 GMT

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.

Re: How to add months in macro variable?

ballardw — Wed, 03 Feb 2021 15:33:58 GMT

@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