topic Re: intnx for previous eom date, not working in proc sql in SAS Programming

intnx for previous eom date, not working in proc sql

Mary001 — Wed, 04 Mar 2020 13:16:28 GMT

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!

Re: intnx for previous eom date, not working in proc sql

Kurt_Bremser — Wed, 04 Mar 2020 13:22:21 GMT

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.

Re: intnx for previous eom date, not working in proc sql

novinosrin — Wed, 04 Mar 2020 13:22:25 GMT

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;

Re: intnx for previous eom date, not working in proc sql

PaigeMiller — Wed, 04 Mar 2020 13:23:19 GMT

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.

Re: intnx for previous eom date, not working in proc sql

Mary001 — Wed, 04 Mar 2020 13:33:09 GMT

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?

Re: intnx for previous eom date, not working in proc sql

Kurt_Bremser — Wed, 04 Mar 2020 13:34:35 GMT

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

Re: intnx for previous eom date, not working in proc sql

Kurt_Bremser — Wed, 04 Mar 2020 13:37:36 GMT

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