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"
&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.
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;
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.
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.
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;
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);
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
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?
&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.
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.
@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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.