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;
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."
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.