topic Re: Read in alphanumeric timestamp values (26 characters long) with suitable informat in SAS Programming

Read in alphanumeric timestamp values (26 characters long) with suitable informat

FK1 — Wed, 10 Nov 2021 11:01:40 GMT

Hi Everyone,

is there a suitable SAS timestamp INFORMAT, which can process a 26 character long timestamp value which includes 6 decimal places?

I only found the ANYDTDTM INFORMAT. But using this informat, the six decimal places are truncated and set to zero.

data have; 
length ts_alphanumeric $26.;
ts_alphanumeric = "2021-10-17-20.00.05.571907";
ts_notyet = input(ts_alphanumeric, ANYDTDTM.);
format ts_notyet datetime25.6;
run;

What I want, is this:

data want;
ts_want = dhms("17OCT2021"d, 20,00,05.571907) ;
format ts_want datetime25.6;
run;

I'd prefer an informat rather than use dateime functions like "dhms", "datepart", etc.

Any suggestions?

Cheers,

FK1

Re: Read in alphanumeric timestamp values (26 characters long) with suitable informat

s_lassen — Wed, 10 Nov 2021 14:03:34 GMT

I think you can use the ymddttm26. informat - it is important to use the length also, as the informat will otherwise stop reading after the last decimal point.

Re: Read in alphanumeric timestamp values (26 characters long) with suitable informat

data_null__ — Wed, 10 Nov 2021 14:28:48 GMT

@s_lassen wrote:

I think you can use the ymddttm26. informat - it is important to use the length also, as the informat will otherwise stop reading after the last decimal point.

Yes YMDDTTM works with wacky delimiters

data _null_; 
   length ts_alphanumeric $26;
   do ts_alphanumeric = "2021-10-17-20.00.05.571907","2021-10-17T20:00:05.571907";
      ts_notyet = input(ts_alphanumeric, E8601DT26.);
      put _all_;
      ts_notyet = input(ts_alphanumeric, ymddttm26.);
      put _all_;
      ts_notyet = input(ts_alphanumeric, ANYDTDTM26.);
      format ts_notyet datetime25.6;
      put _all_;
      end;
   run;

Re: Read in alphanumeric timestamp values (26 characters long) with suitable informat

Tom — Wed, 10 Nov 2021 14:52:39 GMT

You can convert it into a datetime value (number of seconds since 1960) using the YMDDTTM informat.

But you cannot store the full 6 digits of the fractional seconds exactly. The floating point numbers that SAS uses to store numbers only support about 15 decimal digits so the latest date that can be exactly represented as datetime with 6 decimal places is in 1988. Plus you cannot represent decimal fraction exactly in floating point binary anyway.

544   data test;
545     ts_char= "2021-10-17-20.00.05.571907";
546     ts = input(ts_char, ymddttm26.);
547     maxint=constant('exactint')/10E6;
548     put ts_char= /(ts maxint) (=comma32.6)/ (ts maxint)(=datetime25.6);
549   run;

ts_char=2021-10-17-20.00.05.571907
ts=1,950,120,005.571900 maxint=900,719,925.474099
ts=17OCT2021:20:00:05.571907 maxint=16JUL1988:23:58:45.474099

Re: Read in alphanumeric timestamp values (26 characters long) with suitable informat

FreelanceReinh — Thu, 11 Nov 2021 13:39:36 GMT

@Tom wrote:

But you cannot store the full 6 digits of the fractional seconds exactly. The floating point numbers that SAS uses to store numbers only support about 15 decimal digits so the latest date that can be exactly represented as datetime with 6 decimal places is in 1988.

This is an interesting consideration. There are indeed datetime values whose internal representation under Windows or Unix does not change if one microsecond is added, i.e., where the precision of 52 mantissa bits is insufficient. An example, 31DEC9999:23:59:59.999999, was discussed in an earlier thread started by @FK1.

In theory I think the precision should be sufficient for storing datetime values (including microseconds) with an absolute value less than 2**33='15MAR2232:12:56:32'dt (and I haven't found a counterexample yet). Reason: The least significant bit, i.e., the 52nd mantissa bit of such values has a place value <=2**(32-52)=2**-20<1E-6. So the numeric representation error will be <0.5E-6, hence small enough so that rounding the corresponding decimal value to millionths should return the original value. Of course, calculations involving such values may require a greater precision.

In a few tests (with Windows SAS 9.4M5) I made some surprising observations, though:

About 0.004% of a random sample of 10 million datetimes dt with (up to) six decimals in the date range 1 Jan 1900 - 14 Mar 2232 did not satisfy the equation dt = input(put(dt,datetime25.6),datetime25.). But the differences were <=2**-24<6E-8, all examples were datetimes from years 1943-1976, most of them even from 1959 and 1960, i.e., with small absolute values. Example: dt=66.544784.
```
66+544784/1e6 eq 66.544784 ne '01JAN1960:00:01:06.544784'dt eq input('01JAN1960:00:01:06.544784',datetime25.)
```
It appears that writing a datetime as a '...'dt literal or reading the corresponding string with the DATETIME25. informat occasionally leads to a slightly less accurate internal binary representation than writing it as a number or calculating it from integers -- yet without affecting the decimal value rounded to millionths.
About 0.1% of the random sample described above showed a different fractional part when displayed in E8601DT26.6 format than in DATETIME25.6 format. All these cases were negative datetimes from the years 1936-1951. Example: dt='04MAY1951:13:01:56.3852'dt (=-273322683.6148).
```
put(dt,datetime25.6)= '04MAY1951:13:01:56.385200'
put(dt,e8601dt26.6) ='1951-05-04T13:01:56.385201'
```
After correction of the decimals (if needed), reading the E8601DT26.6 representations with the YMDDTTM26. informat resulted in the same values as reading the corresponding DATETIME25.6 representations with the DATETIME25. informat.

Re: Read in alphanumeric timestamp values (26 characters long) with suitable informat

Tom — Thu, 11 Nov 2021 14:00:07 GMT

Thanks for that detailed investigation.

I think that I have seen before that there is also some slight differences in the conversion of done for numeric literals in a program and the result of reading the same string with the INPUT statement (or INPUT() function). Something about the difference in the number of bits uses in the process of converting the strings into binary numbers.

Re: Read in alphanumeric timestamp values (26 characters long) with suitable informat

Kurt_Bremser — Thu, 11 Nov 2021 14:05:21 GMT

@Tom wrote:

But you cannot store the full 6 digits of the fractional seconds exactly.

Unless your SAS runs on z/OS on a zSeries, where you have four more bits in the mantissa.