So if you started with an XSLX file note that the LOCALE used in EXCEL can change how EXCEL displays the exact same values when formatted as "date".
In this XLSX file I typed the same 12/31/2024 string into the cells A2 to A4. I then use the menus to leave the formatting as DATE but change the LOCALE used for each of them.
When I use PROC IMPORT to make a SAS dataset the result is a variable with the MMDDYY10. format attached to it.
If I ask SAS to read each line separately
proc import dbms=xlsx replace
file='c:\downloads\date_regions.xlsx'
out=us
;
range='$A2:C2';
getnames=no;
run;
proc print;
title "US line";
run;
proc import dbms=xlsx replace
file='c:\downloads\date_regions.xlsx'
out=uk
;
range='$A3:C3';
getnames=no;
run;
proc print;
title "UK line";
run;
proc import dbms=xlsx replace
file='c:\downloads\date_regions.xlsx'
out=estonian
;
range='$A4:C4';
getnames=no;
run;
proc print;
title "Estonian line";
run;then I get formats attached the match the display used by the XLSX file.
But in addition to the LOCALE setting that I modified on those individual files I suspect that the XLSX and/or the EXCEL version you are running might have their own settings for LOCALE.
If you want the DATE variables to use the DDMMYY format then tell SAS that.
If you KNOW the names of the variables just add a FORMAT statement at some point in your process.
If you have no idea what date variables you will get then (does not sound like a repeatable process) then first ask SAS to make a list of the names of the DATE variables into a macro variable and use that to write the FORMAT statement.
Let's say you imported the XSLX sheet into a dataset named WORK.TEST using something like this:
proc import dbms=xlsx out=test replace
file="C:\downloads\input_excelfile.xlsx"
;
run;You can then do something like this to change the format attached to ALL of the date variables.
proc contents data=test noprint out=contents;
run;
proc sql noprint;
select nliteral(name) into :datevars separated by ' '
from contents
where fmtinfo(format,'cat')='date'
;
quit;
proc datasets lib=WORK nolist;
modify test;
format &datevars ddmmyy10.;
run;
quit;
As to what changed I suspect that somehow your default location has changed from a country like the United Kingdom that prefers DMY order to a country like the United States that prefers MDY order and either SAS or EXCEL is honoring those changed settings.
To avoid this issue in the future I suggest that switch to use YMD order for you DATE values. That will not get confused.
