topic Re: Read a strange datetime format from a csv in SAS Programming

Read a strange datetime format from a csv

Preguntarius — Fri, 16 Jun 2023 13:42:03 GMT

Hi everyone,

does somebody now how to read a datetime string like "2023-04-30T00:00:00.000+02:00" into a sas table?

I'm looking for a suitable informat. Something like anydtdtm40. (but this doesn't work).

I only need the date, by the way, time is irrelevant for me.

Thanks in advance

Re: Read a strange datetime format from a csv

PaigeMiller — Fri, 16 Jun 2023 13:51:50 GMT

Perhaps this table will be of use: https://documentation.sas.com/doc/en/pgmmvacdc/9.4/leforinforref/n09mk4h1ba9wp1n1tc3e7x0eow8q.htm#p05rz1ood6uhonn1wipzp6j93fxa

data have;
    dt="2023-04-30T00:00:00.000+02:00";
    dt_new=input(dt,E8601DZ30.3);
    format dt_new datetime19.;
run;

Re: Read a strange datetime format from a csv

Preguntarius — Fri, 16 Jun 2023 14:16:06 GMT

Thanks!

Unfortunately, it doesn't work when I read the csv file.

So this part delievers only missings.

data xy;
infile "/----.csv"
 informat ULTIMOTS E8601DZ30.3;
 format ULTIMOTS datetime19.;
 input  ULTIMOTS ;
run;

Re: Read a strange datetime format from a csv

Tom — Fri, 16 Jun 2023 14:24:31 GMT

YYMMDD seems to work fine.

data test;
  infile cards dsd truncover ;
  input id date :yymmdd. ;
  format date yymmdd10.;
cards;
1,"2023-04-30T00:00:00.000+02:00"
;

Obs    id          date

 1      1    2023-04-30

Re: Read a strange datetime format from a csv

Tom — Fri, 16 Jun 2023 14:26:19 GMT

why would you ask it to imply a decimal point?

Re: Read a strange datetime format from a csv

Preguntarius — Fri, 16 Jun 2023 14:29:38 GMT

That's it, thanks a lot!

Re: Read a strange datetime format from a csv

PaigeMiller — Fri, 16 Jun 2023 15:09:16 GMT

But using YYMMDD10. does not take into account the adjustment made for the time zone offset. So I cannot see how that is a correct answer.

Re: Read a strange datetime format from a csv

PaigeMiller — Fri, 16 Jun 2023 15:10:33 GMT

@Preguntarius wrote:

Thanks!

Unfortunately, it doesn't work when I read the csv file.

So this part delievers only missings.
data xy;
infile "/----.csv"
 informat ULTIMOTS E8601DZ30.3;
 format ULTIMOTS datetime19.;
 input  ULTIMOTS ;
run;

When something doesn't work, show us the LOG. (All of the log for this data step, every single line for this data step).

Re: Read a strange datetime format from a csv

Tom — Fri, 16 Jun 2023 15:43:39 GMT

@PaigeMiller wrote:

But using YYMMDD10. does not take into account the adjustment made for the time zone offset. So I cannot see how that is a correct answer.

That is for the original poster to decide.

If they don't care about the time of day then they probably do not care about a 2 hour difference either. It might be that every record has the same midnight with 2 HR offset because the values were generated from a system that only has timestamp data type.

But it it no possible to tell from a sample size of one.

data test;
  infile cards dsd truncover ;
  input id str :$29. ;
  dt = input(str,e8601dz29.);
  date1 = input(str,yymmdd10.);
  date2 = datepart(dt);
  format dt datetime19. date: yymmdd10.;
cards;
1,"2023-04-30T00:00:00.000+02:00"
;

Obs    id                 str                                  dt         date1         date2

 1      1    2023-04-30T00:00:00.000+02:00     29APR2023:22:00:00    2023-04-30    2023-04-29