@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
... View more