topic Re: libname SAS- Oracle dates in SAS Programming

libname SAS- Oracle dates

SASAna — Fri, 14 Dec 2018 04:34:02 GMT

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.

Re: libname SAS- Oracle dates

PGStats — Fri, 14 Dec 2018 04:55:26 GMT

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

Re: libname SAS- Oracle dates

RW9 — Fri, 14 Dec 2018 09:21:55 GMT

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.

Re: libname SAS- Oracle dates

Kurt_Bremser — Fri, 14 Dec 2018 09:51:18 GMT

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