topic Re: SAS DI date field from SAS Data Set to Oracle in SAS Data Management

SAS DI date field from SAS Data Set to Oracle

AaronPK__ — Mon, 07 Aug 2017 05:01:09 GMT

Hello,

I am trying to load data from a SAS Data set to an Oracle table. All the fields expect data date fields are inserted with no problems at all.

One of my date fields is character in SAS dataset and is of datetime format. The other date field is ddmmyy8., In the first case, a blank value is inserted into Oracle table and for the second field 01-JAN-60 is being inserted into Oracle table.

Why doesn't SAS DI not comply with Oracle Date field types? Has anyone faced this problem before? Is this a known buy?

Any ideas??

Thanks!

Re: SAS DI date field from SAS Data Set to Oracle

SASKiwi — Mon, 07 Aug 2017 07:15:10 GMT

Oracle date columns are actually datetimes, so you should have more success if you define your SAS DI date columns as datetime columns (numeric with the DATETIME20. format applied). Here is a useful link that describes how Oracle data types are translated:

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p06jk0u30uhuj5n18fqw9sxr25lk.htm

IMO the issue here is more a case of not assigning the right format to the date(time) columns to be loaded rather than SAS DI not "complying" with Oracle

Re: SAS DI date field from SAS Data Set to Oracle

Patrick — Mon, 07 Aug 2017 10:51:29 GMT

@AaronPK__

For the character field: Convert it to a SAS Date or DateTime column in SAS before you load it into Oracle.

For the SAS Date field: Use format DATE9. for SAS Date values and DATETIME21. for SAS datetime values. For some reasons the SAS/Access engine doesn't play nicely with all OOTB SAS Date and DateTime formats but I know from experience that conversion is working with formats Date and Datetime.

If you don't want to change the formats then you can also use option DBTYPE to control how values from SAS columns get converted to values for DBMS columns. http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371576.htm

Re: SAS DI date field from SAS Data Set to Oracle

AaronPK__ — Mon, 21 Aug 2017 05:50:51 GMT

Thanks for the help fellas, I was able to fix the problem by setting format to DATETIME22.

Cheers,

Aaron