topic Re: how to convert default timestamp ('0001-01-01 00:00:00.000000) in sas in New SAS User

how to convert default timestamp ('0001-01-01 00:00:00.000000) in sas

learn_SAS_23 — Fri, 26 Aug 2022 13:15:35 GMT

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

Re: how to convert default timestamp ('0001-01-01 00:00:00.000000) in sas

AMSAS — Fri, 26 Aug 2022 13:51:27 GMT

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.

Re: how to convert default timestamp ('0001-01-01 00:00:00.000000) in sas

learn_SAS_23 — Fri, 26 Aug 2022 14:04:25 GMT

its a default date in our source system , we are just want to maintain same source data in sas datasets

Re: how to convert default timestamp ('0001-01-01 00:00:00.000000) in sas

FreelanceReinh — Fri, 26 Aug 2022 15:10:18 GMT

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

Re: how to convert default timestamp ('0001-01-01 00:00:00.000000) in sas

ballardw — Fri, 26 Aug 2022 15:53:58 GMT

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.

Re: how to convert default timestamp ('0001-01-01 00:00:00.000000) in sas

learn_SAS_23 — Wed, 31 Aug 2022 11:28:35 GMT

thanks for everyone inputs , from this Atleast DIS doesn't support the dates prior to about 1582 , all are treated as NULL

Re: how to convert default timestamp ('0001-01-01 00:00:00.000000) in sas

Tom — Wed, 31 Aug 2022 13:27:07 GMT

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.