Correct datetime informat for 2020-09-18T17:58:59-0700

[TOM123]

What is the correct informat and format to use for a date-time field that looks like this?:  2020-09-18T17:58:59-0700

I have tried

informat timestamp E8601Dz25.;

format timestamp E8601Dz25.;* B8601DX25.;

input participantId timestamp E8601Dz25. bNumber;

I have tried

informat timestamp B8601DX25.;

format timestamp B8601DX25.;

input participantId timestamp E8601DX25. bNumber;

Regardless, I receive this error message:  NOTE: Invalid data for timestamp in line 1 8-32.

I have tried changing the length to match the number of characters in the date, i.e. 24

informat timestamp E8601Dz24.;

This causes the next variable to be read incorrectly.

[ballardw]

Show the entire log for that data step.

Including the bit where SAS shows the invalid data line.

I think part of your problem is that the time offset is not correct for the informat. Note this works:

data example;
  input x e8601dz.;
  format x datetime16.;
datalines;
2020-09-18T17:58:59-07:00
;

So you need to insert a : in the time offset before using Input. One way, read the value into a string variable long enough to hold the value plus an inserted colon character, do the insert and input the result.

data example;
  length xstr $ 26.;
  input xstr :$25.;
  x = input(catx(':',substr(xstr,1,22),substr(xstr,23)),e8601dz.);
  format x datetime16.;
datalines;
2020-09-18T17:58:59-0700
;

[TOM123]

Can't share the data.

[ballardw · Accepted solution]

---

@TOM123 wrote:

Can't share the data.

---

The issue as I stated is with the value of the offset. If it only occurs for one value in the input file then easiest may be to insert the : in a plain text editor.

If you have a mix of values with the offset having : and some not then you will need to parse the individual values and implement something similar to my second example.

[TOM123]

Thank you.  I will try this.