topic Re: SAS oracle error in SAS Programming

SAS oracle error

suresh123 — Wed, 17 May 2023 01:35:17 GMT

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?

Re: SAS oracle error

SASKiwi — Wed, 17 May 2023 02:02:57 GMT

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

Re: SAS oracle error

LinusH — Wed, 17 May 2023 11:56:22 GMT

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.