libname SAS- Oracle dates

[SASAna]

Hello SAS Users,

I wanted some help to clear this error.

I am merging SAS dataset leftouter join to ORacle table (many to one join), I am getting the dates error. How to resolve this?

PAID_DT variable is DATE format in oracle table.

Libname test oracle     user = &MyId orapw=&mypwd  path = "&Mydb" schema = Test  Oracle_73 = NO;

proc sql;

create table select a.* ,  case when b.CLM_ID is null then '0' else '1' end as test_case

from output_all  a left outer join
                         test.sum_oracle_table (DBKEY=CLM_ID  DBSASTYPE=(PAID_DT=DATE)) b
            on put(datepart(b.PAID_DT),yymmddd10.) between '2017-10-01' and '2018-03-31'
            and a.clm_id = b.clm_id
                  ;
ERROR: Error fetching from cursor. ORACLE error is ORA-01843: not a valid month.

[PGStats]

I guess if you ask for DBSASTYPE=(PAID_DT=DATE), you don't need to use datepart() on paid_dt.

[RW9]

There are several errors and bad coding here before even looking at the code, create table with nothing stipulated, not finished block, upper case coding etc. all of which combine to make the code next to impossible to read.  So:

proc sql;
  create table want as 
  select  a.*,  
          case when b.clm_id is null then '0' 
               else '1' end as test_case
  from    output_all a 
  left outer join test.sum_oracle_table (dbkey=clm_id  dbsastype=paid_dt=date)) b
  on      put(datepart(b.paid_dt),yymmddd10.) between '2017-10-01' and '2018-03-31'
  and     a.clm_id=b.clm_id;
quit;

Now obviously as you have not provided and test data in the form of a datastep, or shown what the data is its hard to guess, but I would say you either have the put() date wrong or the between two text strings (it depends on where the code is executed).  I would suggest that:

  on      b.paid_dt between '01OCT2017'd and '31MAR2018'd

Would be more appropriate, however I cannot try it.

[Kurt_Bremser]

paid_dt is already a date (per your dbsastype= option), so you must not use datepart() on it.

If paid_dt is a datetime value in Oracle, use dbsastype=(paid_dt=datetime).