Hello Team ,
The DB2 table had the value '0001-01-01 00:00:00.000000' in Trans_time column as a default time .
But while reading and loading to a sas data set , this value is converted as null . am using datetime26.6 while defining the column in DIS
it works for all other rows ex : 2022-06-30 04:37:21.973671 , but only the row which has '0001-01-01 00:00:00.000000' is converted in to NULL
Can some one guide , how to convert ''0001-01-01 00:00:00.000000'' value from db2 to sas
Years prior to about 1582 are not valid years because of calendar inconsistencies prior.
I doubt that your data is actually from Year 1 CE(and you really have to be specific about where you are to get January 1 as the calendar has been played with a lot since anything resembling Year 1 CE.
SAS will treat any attempt to use a year prior to 1582 as invalid hence the missing value. I bet that you log shows an "invalid data" message somewhere.
The question is what does that date represent, I'm assuming it's not midnight on the 1st January 0001, and SAS is probably assuming it's an invalid date thus setting it to missing.
Hello @learn_SAS_23,
You could define a custom informat and format. As there is no numeric equivalent in SAS for the datetime "0001-01...", I suggest a special missing value, e.g., .D.
proc format;
invalue DB2dt
'0001-01-01 00:00:00.000000' = .d
other = [e8601dt26.];
value DB2dt
.d = '0001-01-01T00:00:00.000000'
other = [e8601dt26.6];
run;
I'm not familiar with DB2 or DIS, so the example below uses SAS character data.
/* Create example data for demonstration */
data have;
input Trans_time $26.;
cards;
2022-06-30 04:37:21.973671
0001-01-01 00:00:00.000000
;
/* Convert DB2 to SAS datetime values */
data want;
set have;
Trans_dt=input(Trans_time, DB2dt.);
format Trans_dt DB2dt.;
run;
proc print data=want;
run;
Output:
Obs Trans_time Trans_dt 1 2022-06-30 04:37:21.973671 2022-06-30T04:37:21.973671 2 0001-01-01 00:00:00.000000 0001-01-01T00:00:00.000000
Years prior to about 1582 are not valid years because of calendar inconsistencies prior.
I doubt that your data is actually from Year 1 CE(and you really have to be specific about where you are to get January 1 as the calendar has been played with a lot since anything resembling Year 1 CE.
SAS will treat any attempt to use a year prior to 1582 as invalid hence the missing value. I bet that you log shows an "invalid data" message somewhere.
You don't need that in SAS. Unlike DB2 in SAS boolean logic is actually boolean, there are only two outcomes, TRUE or FALSE.
In DB2 comparisons using a null value (what SAS would call a missing value) result in a null result instead of either TRUE or FALSE. Since SAS treats missing as less than any actual number the result will be the same as you get in DB2 by using that ridiculous value.
I am not totally sure how "default" values work in DB2 , but that might also make the missing values in the SAS datasets work when trying to insert observations back into the DB2 table. The missing values might be converted to DB2 null values and thus trigger DB2 to store instead the default value. Try it and see.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.