SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS DI date field from SAS Data Set to Oracle

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

SAS DI date field from SAS Data Set to Oracle

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.

 

DateMapping.png

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!

 

 


Accepted Solutions
Solution
‎08-21-2017 01:51 AM
New Contributor
Posts: 2

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

Posted in reply to AaronPK__

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

 

Cheers,

Aaron

View solution in original post


All Replies
Super User
Posts: 3,254

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

[ Edited ]
Posted in reply to AaronPK__

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#p06jk0u30uhuj5n18f...

 

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 Smiley Happy

Respected Advisor
Posts: 4,173

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

Posted in reply to AaronPK__

@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 

 

Solution
‎08-21-2017 01:51 AM
New Contributor
Posts: 2

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

Posted in reply to AaronPK__

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

 

Cheers,

Aaron

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 201 views
  • 2 likes
  • 3 in conversation