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

datepart and intnx in oracle pass through using proc sql

SkandaPannu — Tue, 21 Nov 2017 08:24:23 GMT

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*/

);

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

PeterClemmensen — Tue, 21 Nov 2017 08:26:32 GMT

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 🙂

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

SkandaPannu — Tue, 21 Nov 2017 08:34:42 GMT

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

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

PeterClemmensen — Tue, 21 Nov 2017 08:39:11 GMT

Post your entire log please 🙂

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

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

Shmuel — Tue, 21 Nov 2017 08:41:22 GMT

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

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

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

SkandaPannu — Tue, 21 Nov 2017 08:50:03 GMT

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')

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

SkandaPannu — Tue, 21 Nov 2017 08:54:38 GMT

same error sir .

Problem with FROM

FROM keyword not found where expected

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

RW9 — Tue, 21 Nov 2017 09:19:20 GMT

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.

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

SkandaPannu — Tue, 21 Nov 2017 09:49:17 GMT

this code is not throwing errors or not returning output

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

RW9 — Tue, 21 Nov 2017 09:52:38 GMT

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.

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

SkandaPannu — Tue, 21 Nov 2017 10:02:20 GMT

should i use curent_date.

not returning errors or ourput