topic Correct datetime informat for 2020-09-18T17:58:59-0700 in SAS Programming

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

TOM123 — Wed, 30 Sep 2020 21:46:10 GMT

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.

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

ballardw — Wed, 30 Sep 2020 22:10:27 GMT

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
;

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

TOM123 — Wed, 30 Sep 2020 22:19:09 GMT

Can't share the data.

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

TOM123 — Wed, 30 Sep 2020 22:21:13 GMT

Thank you. I will try this.

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

ballardw — Wed, 30 Sep 2020 22:31:48 GMT

@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.