BookmarkSubscribeRSS Feed
suresh123
Calcite | Level 5
 

I tried with below  code. 

 %LET START_ORA = %SYSFUNC(sum("&start"d,0),yymmdd10.) ;
      %LET END_ORA = %SYSFUNC(sum("&end"d,0),yymmdd10.) ;

       proc sql;
       connect to oracle(path="xxx" connection =shared);
       create table sample as 
       select * from connection to oracle 
       (select * from ori.auto as a
       where datepart(a.date) between %TSLIT(&START_ORA) and datepart(a.date) %TSLIT(&END_ORA)
       );
       quit;

 

Error :ORACLE prepare error :ORA-00933: SQL command not properly ended. SQL statement : select * from ori.auto as a where datepart(a.date) between '2023-04-30' and datepart(a.date) '2023-04-30'

 

Could you please help me resolve it?

2 REPLIES 2
SASKiwi
Opal | Level 21

You are using a SAS function DATEPART in SQL that needs to be Oracle-compatible. Try removing DATEPART and see how that runs.

LinusH
Tourmaline | Level 20

If data is a datetime column, wy don't you do the bwtween based on datetime constants instead, thus ommitting datepart functions in the where clause.

Then you could even ommit explict SQL pass through.

Data never sleeps

SAS INNOVATE 2024

innovate-wordmarks-white-horiz.png

SAS is headed back to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team.

Interested in speaking? Content from our attendees is one of the reasons that makes SAS Innovate such a special event!

Submit your idea!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 2 replies
  • 113 views
  • 0 likes
  • 3 in conversation