Creating a custom SAS date time format using ISO 8601 notation

2
votes

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;
2
datedatetimesasformatiso

2 Answers

2
votes

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
1
votes

You state you have a character variable containing values that are a date time representation.

The format you link to is not a character format, and thus would not be prefaced with a $. Per docs the format

Writes datetime values for the zero meridian

Datetime values fundamentally are numbers.

You might want to perform an input to convert the character value to an actual SAS datetime value (numeric seconds from epoch 01jan1960T0:0:0), and then apply an ISO datetime format to that variable that is receiving the datetime value.

Example:

data have;
  my_datetime_string = '2020-01-01T00:00:00';
run;

data want;
  set have;
  my_sas_datetime = input (my_datetime_string, E8601DT.);
  my_sas_datepart = datepart(my_sas_datetime);

  format my_sas_datetime datetime19.;
  format my_sas_datepart date9.;
run;

proc print data=want;
run;

enter image description here