Month/day switched when importing cvs file with datetime variable

[Tom]

---

@JKHess wrote:

@Tom Is there additional code needed to run this macro? I'm getting a warning and an error. I think I need to identify where the macro code is found?

%csv2ds("c:\Users\judyk\nycems.csv",out=nycems,percent=1,replace=1);
-
180 WARNING: Apparent invocation of macro CSV2DS not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

---

That error says you did not compile the macro.  Download the csv2sas.sas program file from GITHUB and run it first.  You will also want the parmv.sas file from that same GITHUB repository as %CSV2DS() calls that macro to verify you have passed it valid values and issue standardize notes to the log.

[JKHess]

Thanks so much...I'm running this now. What I'm still confused about is why SAS apparently mixed formats when reading in the file. If it decided that the format was dd/mm/yyyy instead of mm/dd/yyyy, then why did it correctly read in dates that had a 'day' value>12? 

[Patrick]

---

@JKHess wrote:

Thanks so much...I'm running this now. What I'm still confused about is why SAS apparently mixed formats when reading in the file. If it decided that the format was dd/mm/yyyy instead of mm/dd/yyyy, then why did it correctly read in dates that had a 'day' value>12? 

---

To cite @Tom 

The value of the DATESTYLE option drives if an ANY... informat first will attempt to read a date string as mm/dd/yyyy or as dd/mm/yyyy. For a date string with a 'day' value>12 there is only one option how to read the data.

What appears to be the case is that in your environment the datestyle option is set to first try dd/mm but the data uses mm/dd. You just need to change the value of the option to mdy

%let sv_datestyle=%sysfunc(getoption(datestyle,keyword));
options datestyle=mdy;

* your proc import code ;

/* reset option to initial value to not change the behaviour for later code in the same SAS session */
options &sv_datestyle;  

[Tom]

---

@JKHess wrote:

Thanks so much...I'm running this now. What I'm still confused about is why SAS apparently mixed formats when reading in the file. If it decided that the format was dd/mm/yyyy instead of mm/dd/yyyy, then why did it correctly read in dates that had a 'day' value>12? 

---

SAS did NOT switch format (I think you meant INFORMAT).

The problem is that the file you get with the Export CSV option uses an AMBIGUOUS style for the text of the DATETIME fields.  It has text in the style AA BB CCCC DD:EE FF.   That is why I said to use the file produced by the API url instead.  That file has the datetime strings in the unambiguous ISO format of YYYYMMDDThhmmss.

 PROC IMPORT did not know of a SAS informat that could read that so it used ANYDTDTM informat which can handle many different styles, picking the one it thinks best matches the current string's value. 

When the value of either AA or BB is larger than 12 then it is obvious which one is the DAY and which one is the MONTH.  But for a value like '01 04 ...' either one could be the month.     In that case the ANYDT... informats use the setting of the DATESTYLE system option. 

That is normally set to use the order most often used by the country indicated by the LOCALE setting.  So in the US it would assume 01 04 is January fourth.  But in Europe it might assume you wanted first of April.

If you have one order you want it to use the set the DATESTYLE option before the call to PROC IMPORT.  

Since this data is from NYC you should set DATESTYLE to MDY.

[JKHess]

Thank you. I looked in my sasv9 config file to see what LOCALE was set to...it looks like it's set to the US.

In this case, do you know why, when it encountered an ambiguous value (e.g., 01 04), it didn't use the US default (mm/dd/yyyy)?

-LOCALE en_US

[Tom]

---

@JKHess wrote:

Thank you. I looked in my sasv9 config file to see what LOCALE was set to...it looks like it's set to the US.

In this case, do you know why, when it encountered an ambiguous value (e.g., 01 04), it didn't use the US default (mm/dd/yyyy)?

-LOCALE en_US

---

Run your own test and see.

data test;
   input @1 anydate anydtdte10. @1 mdy mmddyy10. @1 dmy ddmmyy10. ;
   format anydate mdy dmy date9.;
cards;
01/04/2023
;

On my machine I get:

Obs      anydate          mdy          dmy

 1     04JAN2023    04JAN2023    01APR2023

Because my settings are:

653  %put %sysfunc(getoption(datestyle)) %sysfunc(getoption(locale));
MDY EN_US

Now try changing the value of DATESTYLE option and see how it works.

If I change DATESTYLE to DMY then I get this result:

Obs      anydate          mdy          dmy

 1     01APR2023    04JAN2023    01APR2023

[Kurt_Bremser]

---

@JKHess wrote:

Thanks so much...I'm running this now. What I'm still confused about is why SAS apparently mixed formats when reading in the file. If it decided that the format was dd/mm/yyyy instead of mm/dd/yyyy, then why did it correctly read in dates that had a 'day' value>12? 

---

This is why you

• don't use PROC IMPORT for csv files; instead write the DATA step yourself, according to the description of the file
• don't use the ANY... informats in your DATA step. Either use a fitting available informat, or read the data as a string and parse it

[Tom]

PROC IMPORT will use ANY... series of informats.  Those will use the setting of the DATESTYLE option to decide what to do with ambiguous strings like 01/04/2023 which could mean first of April to some people and January fourth to others.

But for a CSV file just write you own data step to read the file and you can use the appropriate informat instead.  MMDDYY or DDMMYY.