Desktop productivity for business analysts and programmers

Using INTNX in Proc SQL gives error

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Using INTNX in Proc SQL gives error

Hi Forum,

 

I have a dataset that contains a Date Time field.

 

So I wrote a Proc SQL statement with the below WHERE Clause.

 

I want to use the 'INTNX' Function to match the Date Time to a MthYr value such as below:

 

WHERE intnx('month',datepart(c.DATETIME),0,'e') = 'MAY18'

ERROR: ORACLE prepare error: ORA-00904: "INTNX": invalid identifier

I'm getting an 'Invalid Identifier' Error.

 

However, if I add a query builder to the existing table and create an expression using the exact same statement, it works?

 

Can anyone advise why this is so & how I need to write the WHERE clause so it will run?

 

Thanks


Accepted Solutions
Solution
‎06-21-2018 11:37 PM
Super User
Posts: 10,599

Re: Using INTNX in Proc SQL gives error

In an explicit pass-through, you need to use functions that the target database understands. intnx is not a function in Oracle SQL.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎06-21-2018 11:37 PM
Super User
Posts: 10,599

Re: Using INTNX in Proc SQL gives error

In an explicit pass-through, you need to use functions that the target database understands. intnx is not a function in Oracle SQL.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 16

Re: Using INTNX in Proc SQL gives error

Posted in reply to KurtBremser

Ah, Of Course!

 

Thanks Kurt.  :  >

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 113 views
  • 1 like
  • 2 in conversation