topic Re: Infile CSV Issue with Date Time Format in SAS Programming

Infile CSV Issue with Date Time Format

shellp55 — Mon, 17 Feb 2020 20:28:09 GMT

Hello

I am using SAS 9.4.

I have a csv file where all the date/time values are m/dd/yy h:mm and they aren't importing correctly even if using the anydtdtm format. This makes sense of course because the month could be 1 or 2 characters as could the days. But how do I fix this other than importing as text and parsing out each date/time component and then building the fields again. I know how to do the latter, was just hoping there was something easier.

Any and all assistance appreciated.

Re: Infile CSV Issue with Date Time Format

mkeintz — Mon, 17 Feb 2020 20:54:30 GMT

Sample data please.

Re: Infile CSV Issue with Date Time Format

shellp55 — Mon, 17 Feb 2020 21:39:14 GMT

I was wrong...if I open the file in Notepad then it shows as one would expect: mmddyy hh:mm:ss so it's unclear why it wouldn't be importing correctly.

04/03/18 17:00:00 imports as March 4, 2018 instead of April 3, 2018.

Re: Infile CSV Issue with Date Time Format

Cynthia_sas — Mon, 17 Feb 2020 21:43:08 GMT

Hi:

Without seeing your data and your code, ALL of your code, it's hard to comment. This worked for me. I'm not understanding the issue.

Cynthia

Re: Infile CSV Issue with Date Time Format

mkeintz — Mon, 17 Feb 2020 21:52:07 GMT

Mine comes out as April 3:

Editted below (I sent the first versino, not the final version.

data t;
  input dt1 anydtdtm.;
  put dt1=datetime20.;
datalines;
04/03/18 17:00:00
;

which produces in the log:

64   data t;
65     input dt1 anydtdtm.;
66     put dt1=datetime20.;
67   datalines;

dt1=03APR2018:17:00:00

NOTE: The data set WORK.T has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

What is the LOCALE of your system, mine is LOCALE=EN_US. You can see yours via this program:

proc options option=locale;run;

Re: Infile CSV Issue with Date Time Format

Cynthia_sas — Mon, 17 Feb 2020 21:52:41 GMT

Hi:

Interesting, it works for me:

Cynthia

But you might check the doc and the DATESTYLE option. The ANYDTDTM doc says:

the value of my DATESTYLE option is MDY.

Re: Infile CSV Issue with Date Time Format

shellp55 — Mon, 17 Feb 2020 21:50:44 GMT

Thanks Cynthia. I did exactly as you did in your example with my data, including the format in the proc print and it is not correctly recognizing the date/time even though the format is standard. I guess I'm going to have to import as string and create the date/time values from there.

Re: Infile CSV Issue with Date Time Format

Cynthia_sas — Mon, 17 Feb 2020 21:53:25 GMT

Hi,
I suggest that you experiment with the DATESTYLE option as outlined in the documentation first.
Cynthia

Re: Infile CSV Issue with Date Time Format

shellp55 — Mon, 17 Feb 2020 22:04:16 GMT

Okay, I found out that my datestyle is dmy thus the issue i'm having. How do i change it? Thanks.

Re: Infile CSV Issue with Date Time Format

shellp55 — Mon, 17 Feb 2020 22:14:44 GMT

For anyone else looking at this thread, the issue was my datestyle option as Cynthia surmised. Thanks Cynthia!

I first looked at what my datestyle was: proc options option=datestyle;run;

Once I confirmed it was dmy I entered options datastyle=mdy; to change it to the format I wanted.

Re: Infile CSV Issue with Date Time Format

Tom — Tue, 18 Feb 2020 00:56:54 GMT

@shellp55 wrote:

For anyone else looking at this thread, the issue was my datestyle option as Cynthia surmised. Thanks Cynthia!

I first looked at what my datestyle was: proc options option=datestyle;run;

Once I confirmed it was dmy I entered options datastyle=mdy; to change it to the format I wanted.

Just to clarify a little. The reason the DATESTYLE option has any impact is because you are using a guessing procedure, the ANYDTDTM informat, so SAS has to guess whether 10/12 is Octobter 12th or 10th of December. The DATESTYLE setting is how the ANYDTxxx informats decide which date is meant.

There are two causes for why you had to use the guessing procedure.

The main one is that the file was generated in an ambiguous style. If the values had been presented in Y-M-D order or in ddMONyyyy order then there would have been no way to mistake them. Note also that your date values only include two digits for the year. So what year does 18 represent? is that 2018 or 1918 or 1818 or 2118 or ??.

The secondary one is that while SAS does have a format for reading datetimes with the date in MDY order (MDYAMPM) it does not have one to read them in DMY order (perhaps they should make a DMYAMPM informat?). So users are forced to use the guessing information instead.

If you are lucky and the dates are in fact in MDY order then use the MDYAMPM informat and avoid the guessing.

399   data t;
400     input @1 dt1 anydtdtm20. @1 dt2 mdyampm20. @1 dt3 dmyampm20.;
                                                          ----------
                                                          485
NOTE 485-185: Informat DMYAMPM was not found or could not be loaded.

401     put (dt:) (=datetime20.);
402   datalines;

NOTE: Invalid data for dt3 in line 403 1-8.
dt1=03APR2018:17:00:00 dt2=03APR2018:17:00:00 dt3=.
RULE:       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+
403         04/03/18 17:00:00
dt1=1838394000 dt2=1838394000 dt3=. _ERROR_=1 _N_=1
NOTE: The data set WORK.T has 1 observations and 3 variables.

Re: Infile CSV Issue with Date Time Format

shellp55 — Tue, 18 Feb 2020 01:22:06 GMT

I knew that it impacted the "any date" options but I didn't know what informat to select for the data format I had. The information you provided is really helpful, thanks so much for posting!

Re: Infile CSV Issue with Date Time Format

Tom — Tue, 18 Feb 2020 01:31:56 GMT

You should really check if you can get whoever sent you the file to fix it. Those two digit years are especially going to be a problem. A lot of SAS users were surprised in January of 2020 that reading the 7 character SYSDATE macro variable made it look like their jobs ran in 1920! They were still running with YEARCUTOFF option set to 1920.

Re: Infile CSV Issue with Date Time Format

Kurt_Bremser — Tue, 18 Feb 2020 07:56:26 GMT

@shellp55 wrote:

I was wrong...if I open the file in Notepad then it shows as one would expect: mmddyy hh:mm:ss so it's unclear why it wouldn't be importing correctly.

04/03/18 17:00:00 imports as March 4, 2018 instead of April 3, 2018.

I'd rather read that as March 18, 2004.

This just illustrates the stupidity of not using ISO-formatted dates and times, and (still!) using 2-digit years. After the Y2K scare, this makes me doubt my faith in the future of mankind.