12-05-2016 08:43 AM
12-05-2016 08:51 AM
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.
12-05-2016 09:22 AM
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.
12-05-2016 10:04 AM
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.
12-05-2016 10:18 AM
12-05-2016 10:22 AM
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.
12-05-2016 10:38 AM
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?
12-05-2016 11:00 AM
12-05-2016 11:27 AM
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.
12-06-2016 07:08 AM
12-07-2016 04:30 AM - edited 12-07-2016 04:32 AM
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"
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.
12-05-2016 09:21 AM - edited 12-05-2016 09:25 AM
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.