Hello,
Recently we are hitting a problem in date format when we read data from external file using proc import and we are finally exporting into a csv file. The dates are exporting into csv as US format ( not UK ).
Eg: The excel_date in excel_test.xlxs file is 12/01/2024 ( dd/mm/yyyy )
proc import;
out=test_import ( keep = excel_date )
Datafile= excel_test.xlxs
dbms=xlsx;
run;
data test1;
format new_date date9,;
set test_import;
new_date = excel_date;
run;
proc export data = test1
outfile="new_date.csv"
dbms=csv;
run;
When we run the code, output of sas dataset (test1) as
new_date excel_date
12JAN2024 01/12/2024 ( getting changed here)
While exporting into csv ( new_date.csv ) from test1 sasdataset ,
new_date is exported as 12JAN2024 and excel_date as 01/12/2024 which matches the dataset value. But the actual value for excel_date value in the excel as 12/01/2024 but the value gets changed in csv for that column.
If the value of date in excel is 27/01/2024 , then the csv export dont have any problem as it export the value correctly to csv. I suspect the problem is when the value in the excel file has date value less than or equal 12. If the date is greater than 12 , sas not able to convert as we dont have month greater than 12.
I was running below query to check anything got changed, but no luck
proc options option=(DATESTYLE LOCALE) value;run;
Option Value information for SAS Option Datastyle
Value : MDY
Option Value information for SAS Option Locale
Value: EN_GB
Only recently we are getting this problem, until last month there were no issue. Even in linux OS where SAS is installed ,we do have locale setting as en_GB.
It seems the excel date values are stored in sas dataset as US format.
Please let me know if there is a fix to resolve the issue. We cant change sas code as there are lot of sas code has date values and as I said , this code was working for years.
Thanks
... View more
