how to read Oracle datetime to SAS DI studio

[ganeshmule]

hi all, i have date column with date like "01JAN0001:00:00:00" it is oracle by default format and want to read it in SAS DI as it is,so anybody know how to do it in SAS DI where i can use expression to retrieve that as it is in sas di. example: oracle table SAS TABLE DATE_OF_BIRTH DATE_OF_BIRTH 01JAN0001:00:00:00=======> 01JAN0001:00:00:00 ..THANKS IN ADVANCE

[Kurt_Bremser]

SAS will only work with dates and datetimes after the global acceptance of the Gregorian Calendar, so values like 0001-01-01 can't be displayed and reasonably stored.

Since these dates are usually used to indicate missing values, I suggest to read the formatted Oracle value into a SAS character variable and convert accordingly.

[ganeshmule]

but how to do it please can you explain step by step..

[Kurt_Bremser]

I guess that, when directly importing such dates/datetimes you end up with missing values in SAS. Since you cannot use the 0001-01-01:00:00:00 anyway, that should suffice.

If you want to have other special values instead of 0001-01-01, you could convert the data on the Oracle side to a character representation, read that, and then do something like

if date_char = '0001-01-01'
then date_num = '01jan1600'd;
else date_num = input(date_char,yymmdd10.);

Since we do not use a direct connection to our DBMS (UDB aka DB/2), we extract the tables into fixed-format flat files and read from there. Since the DBMS exports dates into readable format anyway, the conversion works as described.

[LinusH]

How do you plan to use dates like this?

If they are used to store a lower interval date value, converting to the default SAS date '01jan1960' may be sufficient.

But if that date means that there is no relevant date information, you could might leave it as missing.

If you are reading from a newly built system, you could try to convince them to change the default to something nearer in time...

If the trick with comparing to SAS date 01Jan1960 doesn't work for some reason, you could us the DBSASTYPE data set option to convert it to text, and then use conditional INPUT() function to store it a SAS datetime value.

[ganeshmule]

Hi
thank u for your quick answer but I used that option into SAS DI. I have an
Oracle table registered through preassigned library in which I have default
date set as 01-01-0001 which is not readable into sas. I tried this option
already with changing that variables data type from datetime to char but
looks like in sas di it does not work out.
When I change this option named DBSASTYPE it simply don't work or else it
throws an error when u try to open that table. That error was related sas
metadata reading as i suppose from it.
Please let me know if u have used it correctly.
Thanks

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Kurt_Bremser]

Since 01-01-0001 can never be used in SAS, you either

- have to live with it (messages during import and missing values in SAS)

- have to have it converted on the Oracle side (either to character or to another defined value like mentioned) before you import into SAS

- have the data exported to a flat file where you can read it as any type you like (and convert it as needed), since it is exported to a formatted text string anyway.

[LinusH]

I must admit that haven't use DBSASTYPE with a metadata registered table before, so I may need to test this myself.

Have you used DBSASTYPE during table registration?

Also, when you say pre-assigned, what kind of pre-assignment do you use?

What does a PROC CONTENTS of that table show?

[ganeshmule]

Hi
Preassigned library of Oracle on which all tables are registered of the
Oracle's so that tables data when we going to retrieve into sas di that
time there some columns which has Oracle default date and that date show me
blank but if that tables i opened in oracle so I seen there is default date
01jan0001 so I want to assess that date in sas di and making tabkes perfect
....

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Tom]

Sounds like the tables are already "perfect", or at least as close as you can get when converting from Oracle datetime values to SAS datetime values.

[ganeshmule]

Yes..Oracle table is perfect on there side but issue with SAS DI studio it
will not able to get that datetime in sas di studio table..

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Patrick]

@ganeshmule

Oracle table is perfect on there side but issue with SAS DI studio

Other's have already explained this but I believe you haven't fully grasped the concept yet. If you can transfer this Oracle DATE value into SAS without an error and it then shows in SAS as a missing then on both sides in Oracle and in SAS things are perfect in the sense of "as designed".

SAS only handles dates since the Gregorian Calendar reform in 1582. The Oracle DATE of the year 1 is before this date.

"The earliest date that SAS can handle with this algorithm is January 1, 1582"

http://support.sas.com/publishing/pubcat/chaps/59411.pdf 

There are cases where there is simply no 1:1 conversion possible between Oracle and SAS. Another such case would be a NULL value in Oracle. That's a concept which doesn't exist in SAS where a NULL and a Missing and for character values a Blank is the same (represented as a Blank).

It's by the way not DIS - which is the client - but the SAS server which does this conversion to a missing.

The question you need to answer for your use case:

Do you need to be able to distinguish between NULL, missing and year 01 or is it good enough if everything is represented as missing on the SAS side? If it's not good enough then the only way to go is to convert the DATES on the Oracle side to strings - you could for example define an Oracle view which does this conversion as part of a CASE statement; and you then access this view via SAS. BUT: If you convert the dates to strings then you can't use these "dates" anymore for calculations like adding a day, or shift the date to end of the month using SAS functions like intnx().

Give it a thought and choose the option which suits your use case. 

[Tom]

Try looking for a value that is less than a datetime value that SAS can express.

where DATE_OF_BIRTH  < '01JAN1600:00:00'dt

If it gets run on the SAS side the the missing value that the invalid datetime gets translated to will meet the condition.

If it gets run on the Oracle side the the date in the year 1 will be less than a date in the year 1600 so it will also match.

This assumes your data doesn't have other dates before the year 1600 or null values that you want to distinguish from the invalid year 1 value.