BookmarkSubscribeRSS Feed
SkandaPannu
Calcite | Level 5

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

);

10 REPLIES 10
PeterClemmensen
Tourmaline | Level 20

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 🙂

SkandaPannu
Calcite | Level 5

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

 

PeterClemmensen
Tourmaline | Level 20

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
SkandaPannu
Calcite | Level 5

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

Shmuel
Garnet | Level 18

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

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

SkandaPannu
Calcite | Level 5

same error sir .

 

Problem with FROM 

FROM keyword not found where expected

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SkandaPannu
Calcite | Level 5

this code is not throwing errors or not returning output

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SkandaPannu
Calcite | Level 5

should i use curent_date.

not returning errors or ourput

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 4965 views
  • 0 likes
  • 4 in conversation