Infile CSV Issue with Date Time Format

[shellp55]

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.

[mkeintz]

Sample data please.

[shellp55]

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.  

[mkeintz]

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;

[Cynthia_sas]

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.

[Kurt_Bremser]

---

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

[Cynthia_sas]

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

[shellp55]

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.  

[Cynthia_sas · Accepted solution]

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

[shellp55]

Hi

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

[shellp55]

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.  

[Tom]

---

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

[shellp55]

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!  

[Tom]

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.