@Kurt_Bremser Thank you very much for your great support. I am really appreciated your time and effort in helping people out :). the data itself is nice and clean. but it will be converted to wrong dates if it were not using the format starting with mm instead of dd. (error occurred when days number <=12) So, it worked when I manually changed the format to mm/dd/yy in excel. This is not an ideal solution and needs extra step on a daily basis. ... View more

put the story short: The dates will be correctly imported when csv with data formatted below (mm/dd/yyyy): 03/14/2020 0:00 03/15/2020 0:00 03/15/2020 0:00 Some dates will be wrong when import from CSV with below data (yyyy-mm-dd): 2020-03-17 00:00:00 2020-03-10 00:00:00 or (dd/mm/yyyy): 14/03/2020 0:00 09/03/2020 0:00 10/03/2020 0:00 ... View more

03/17/2020 0:00 03/10/2020 0:00 03/11/2020 0:00 03/11/2020 0:00 03/16/2020 0:00 03/14/2020 0:00 03/14/2020 0:00 03/14/2020 0:00 03/14/2020 0:00 03/14/2020 0:00 03/14/2020 0:00 03/14/2020 0:00 03/15/2020 0:00 03/15/2020 0:00 ... View more

Hi, my dear helpers 🙂   This issue exist when import CSV from PC to server.   I had an earlier issue which is similar but exist when export data to CSV from sas server to PC.  It was resolved with the help from @Reeza who suggested change dates format from ddmmyy to mmddyy as that is what excel likes (I guess it is to do with default settings in excel). I don't have any export dates issue ever since I changed the date format from ddmmyy10. to date9. for all sas data sets to be exported.  This stopped excel to incorrectly switch numbers (under 13) between month and day.   I finally managed a work around to ensure dates are correctly imported from CSV file from PC to server by change the format in CSV(in excel) to mmddyy format (customized format).   I wondered if there is an option set the default dates format that will in line with default settings between excel/csv to SAS environment?   in the macro there is nothing set the dates format, but the log shows the date format(datetime.) and informat(anydtdtm40.):     ... View more

Hi Thank you all for your suggestions. My apologies not making the issue easier for helpers 🙂   there are a couple of macros being used in the programme to transfer CSV file from PC to server. The datetime column in csv looks like this (note the difference in the formula field to the cell):   Below are the codes used to get the data:   %FTP_SAS_PC(test_date,Import); data test; set test_date; format date DTDATE9.; run; proc sort data=test nodupkey;by date; run;   the test data will show some incorrect dates where the day & month being misplaced.   after changed the format for the dates column in CSV from ddmmyyyy to mmddyyyy, the issue resolved using the same code above.       When I changed the format to       ... View more

Perfect! This is what I want.    Thanks a lot 🙂   @ChrisNZ  ... View more

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. ... View more

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 🙂     ... View more

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