topic How to get current month and last three months data using pass thru query? in SAS Programming

How to get current month and last three months data using pass thru query?

MSK4 — Tue, 13 Jul 2021 07:28:51 GMT

proc sql;

connect to oracle as myconn(user=xx pass=xx path=xx);

select * from connection to myconn

(

select * from tranx where datepart(trandate)=intnx('month',trandate,-3)

);

disconnect from myconn;

quit;

Throwing error invalid intnx identifier

Please help me out. Many thanks;

Re: How to get current month and last three months data using pass thru query?

tarheel13 — Tue, 13 Jul 2021 07:37:48 GMT

Like @Kurt_Bremser said https://communities.sas.com/t5/SAS-Enterprise-Guide/Using-INTNX-in-Proc-SQL-gives-error/td-p/472356 "In an explicit pass-through, you need to use functions that the target database understands. intnx is not a function in Oracle SQL."

Re: How to get current month and last three months data using pass thru query?

Kurt_Bremser — Tue, 13 Jul 2021 07:38:15 GMT

When you use explicit pass-through, you must work in the remote environement, which means in your case strict Oracle syntax. Neither the DATEPART nor the INTNX function are available in Oracle, so you need to use the respective Oracle functions for that. Datetime to date will probably be a cast. Also keep in mind that databases use a different system of literals for dates etc.

And you need to review your logic. This condition will never be true during the existence of the universe:

datepart(trandate)=intnx('month',trandate,-3)

A date can never be equal to the same date minus three months, and if you need to use DATEPART because trandate is a datetime, then you need to use it in other places also.

The INTNX function does also allow datetime related intervals, like dtmonth, which would allow you to use the datetime directly, but ONLY IN SAS!

I suggest that you test your query first in the native Oracle client, and once it works there, see how you can send it from SAS.