I have a character variable in a SAS table that is populated with a non-usual type of date time. Indeed, it has the following format : YYYY-MM-DDT00:00:00.000+00:00. Here is an example: 2020-01-01T00:00:00.
I want to transform this character column to a date column. I have been searching in the documentation an associated format but did not find the correct one.
I found $E8601DZ. ($E8601DZ. format) which results for example in: 2020-01-01T00:00:00+00.00 (notice the .000 missing after the seconds) and $E8601DTw.d ($E8601DTw.d format) which results for example in: 2020-01-01T00:00:00.000 (notice the +00:00 missing).
Thus, would it be possible to "mix" those two formats resulting in a custom format that will create a date variable with the desired format ?
SAS Code:
data example;
date="2020-01-01T00:00:00.000+00:00";
run;
Just use the right informat to read the data and the right format to write the data. If the offset is always zero use the E8601DZ format/informat. To include three decimal places just make sure to specify that is what you want. The total width of the generated string is 29 characters of which 3 are to the right of the decimal point, so use e8601dz29.3 format specification. You do not want to include the decimal width in the INFORMAT as the meaning of decimal places on an informat is different than on a format, so use e8601dz29. as the informat specification.
data example;
string="2020-01-01T00:00:00.000+00:00";
datetime=input(string,e8601dz29.);
format datetime datetime19.;
string2=put(datetime,e8601dz29.3);
put (_all_) (=/);
run;
Results:
string=2020-01-01T00:00:00.000+00:00
datetime=01JAN2020:00:00:00
string2=2020-01-01T00:00:00.000+00:00