DATA Step, Macro, Functions and more

datepart and intnx in oracle pass through using proc sql

Reply
Occasional Contributor
Posts: 6

datepart and intnx in oracle pass through using proc sql

Hi ,

I have the following code throwing errors.Can you please help me with correct code

 

proc sql;

connect to oracle

(DEFER=XXX PATH=XXXX AUTHDOMAIN="XXXXXXXX"

);

create table temp as 

select *

from connection to ORACLE

(

select  at_date,

           datepart(at_date) as date

           shop

from   ABC.GYGYGYG

where 

      datepart(at_date)=intnx('month','today()',-1,'e')/*want to get last date of last month*/

);

PROC Star
Posts: 1,400

Re: datepart and intnx in oracle pass through using proc sql

Posted in reply to SkandaPannu

Post your log, that makes it easier to see where your code fails..

 

But first off, the today() function in your INTNX function should not be in quotation marks Smiley Happy

Occasional Contributor
Posts: 6

Re: datepart and intnx in oracle pass through using proc sql

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected.

 

PROC Star
Posts: 1,400

Re: datepart and intnx in oracle pass through using proc sql

Posted in reply to SkandaPannu

Post your entire log please Smiley Happy

 

Also, you need a comma between date and shop in the code below

 

select  at_date,
           datepart(at_date) as date /* You need a comma here */
           shop
from   ABC.GYGYGYG
Occasional Contributor
Posts: 6

Re: datepart and intnx in oracle pass through using proc sql

proc sql;

connect to oracle

(DEFER=XXX PATH=XXXX AUTHDOMAIN="XXXXXXXX"

);

create table temp as 

select *

from connection to ORACLE

(

select  at_date,

           shop,

           datepart(at_date) as date

           

from   ABC.GYGYGYG

where 

      datepart(at_date)=intnx('month',today(),-1,'e')  /*want to get last date of last month*/

);

 

 

log:

 

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL Statement: select at_date,shop,datepart(at_date) as date from ABC.GYGYGYG 

where 

      datepart(at_date)=intnx('month',today(),-1,'e') 

Trusted Advisor
Posts: 1,848

Re: datepart and intnx in oracle pass through using proc sql

[ Edited ]
Posted in reply to SkandaPannu

You "want to get last date of last month" - 

you can do it by today() - day(today()).

Occasional Contributor
Posts: 6

Re: datepart and intnx in oracle pass through using proc sql

same error sir .

 

Problem with FROM 

FROM keyword not found where expected

Super User
Super User
Posts: 9,799

Re: datepart and intnx in oracle pass through using proc sql

Posted in reply to SkandaPannu

Corrected your code:

proc sql;
  connect to oracle (DEFER=XXX PATH=XXXX AUTHDOMAIN="XXXXXXXX");
  create table temp as 
  select *
  from connection to ORACLE
  (select  at_date,
           datepart(at_date) as date,
           shop
   from   ABC.GYGYGYG
   where datepart(at_date)=intnx('month',today(),-1,'e'));
  disconnect from oracle;
quit;

Note than I put a comma after the "as date" part which you had missing, and removed today() from quotes. 

 

Do note however, I doubt today() will work as that is a SAS function.  If your passing SQL through to the database, then you need to use SQL and functions appropriate to the SQL database, you would need to use SYSDATE or CURRENT_DATE according to the Oracle documents:

https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions037.htm#SQLRF00628

 

Of course you could drop the where clause completely, return all the records and then filter it in a simple datastep afterwards.

Occasional Contributor
Posts: 6

Re: datepart and intnx in oracle pass through using proc sql

this code is not throwing errors or not returning output

Super User
Super User
Posts: 9,799

Re: datepart and intnx in oracle pass through using proc sql

Posted in reply to SkandaPannu

And I can see these through the magic of guesswork?

 

Post logs outputs showing errors, post the code used which generates those errors!

 

As I said, today() will not work in Oracle - it doesn't exist, you need to use Oracle specific SQL.

Occasional Contributor
Posts: 6

Re: datepart and intnx in oracle pass through using proc sql

should i use curent_date.

not returning errors or ourput

Ask a Question
Discussion stats
  • 10 replies
  • 451 views
  • 0 likes
  • 4 in conversation