Export to CSV : some records have correct date, some not

[Suzy_Cat]

Hi there,

Need advice on why the dates showing incorrect when exported to csv. The data looks absolutely fine in SAS but some random lines would show incorrect dates "mm/dd/yyyy" where it should be "dd/mm/yyyy". Most of the records still have correct date. It is really annoying. Hope some one can shed me some light here 🙂

in excel, the first line looks normal as 02/01/2018, but it is wrong, in SAS the correct date is 01 Feb 2018.

     

When this issue occurred, you can always found there are two sets of data there:

one is selectable by year/month/date, the other one can only be selected by the date itself.

[Reeza · Accepted solution]

Excel is changing the datatype when you open it in Excel. To avoid this issue, open Excel, go to the Data tab>Import txt/CSV file. There's a button to say don't guess at types, select that and then format the data the way you need it.

Or export your data straight to Excel and it should avoid the issue altogether.

Or try changing the export format from SAS to be mmddyy as that's what Excel likes.

[Suzy_Cat]

Thank for your help Reeza! I think I may got the right solution. I will test it tomorrow. Finger crossed!

[Suzy_Cat]

Hi Reeza,

Thanks heaps for your help. You have pointed me out to the right direction.! How awesome is that... could not imagine if I don't have an answer to deal with the issue (was really annoying).

Now I have replaced all dates format from ddmmyy10. to date9. in SAS. Looks like the exported data are perfectly fine now in excel 🙂

[Suzy_Cat]

Further more, in case any one may be interested, it is similar when using format mmyy5.(or mmyy7.)

I have found using format monyy7. is way more better if there is need to export to CSV/EXCEL.