intnx for previous eom date, not working in proc sql

[Mary001]

Hi All,

I have a proc sql (SAS EG 7.1) and in the where clause is a date variable but instead of putting a hard date, I would like to use something similar to the intnx function to dynamically get the previous month's date.

Users will be running the sql at any given time and it should always use the previous EOM date.

here's what I have now ...

select *

from abc

where date = '1/31/2020'

I've tried this and get this error, SQL0440N No authorized routine named "TODAY" of type "FUNCTION"
having compatible arguments was found. SQLSTATE=42884 

select *

from abc

where date = intnx('month',today(),-1,e)

I hope this is something simple, can someone please help? Thank you so much in advance!

[Kurt_Bremser]

SQL0440N is a IBM DB/2 ERROR code, so this happens in a piece of explicit pass-through code. In explicit pass-through, you have to use the syntax of the target DBMS.

[Kurt_Bremser]

I have found this in https://it.toolbox.com/question/last-and-first-date-for-a-month-in-db2-120308:

In DB2, the last day of last month is:

CURRENT DATE - (DAY(CURRENT DATE)) DAYS

The first day of this month is:

CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS

[novinosrin]

Hi @Mary001  I tested the below(your code) and it seems to work fine

proc sql;
select *

from abc

where date = intnx('month',today(),-1,'e');
quit;

[PaigeMiller]

Is your SQL trying to extract from a database via a passthru? Can you show us the entire PROC SQL and not selected parts?

The TODAY() function does not work with most databases in a passthru.

[Mary001]

Hi All,

Yes, the sql is going to a db2 database to get the filtered data.

"The TODAY() function does not work with most databases in a passthru." = Does this mean I need to find a db2 solution?

[Kurt_Bremser]

---

@Mary001 wrote:

Hi All,

Yes, the sql is going to a db2 database to get the filtered data.

"The TODAY() function does not work with most databases in a passthru." = Does this mean I need to find a db2 solution?

---

Absolutely. As soon as you enter explicit pass-through, you (temporarily) leave the world of SAS.