I have an issue on SAS 9.4, using proc export.
The dataset to be exported, has a date field which is not exporting correctly.
For all records where the value of the date field in the original file is <= 12/31/1959,
then the value of that field in the exported XLSX file, is 12/31/1959.
I have changed the values in that column by hand before exporting to check,
and found that 12/31/1959 is the boundary line between exporting correctly and not.
To make sure the problem wasn't (say) corrupted data, I also tried adding 20 years
to each value in the date field using INTX, before exporting, so that all values were > 12-31-1959,
and all the new values were exported to .XLSX correctly.
The syntax of the export is
Proc Export data=work.dataset
outfile = "/mydir/outfile.xlsx"
dbms=xlsx
replace label;
run;
Incidentally, this issue occurs when the field in the exported file is in DDMMYY10. format
(which fulfills a business rule), but does *NOT* occur when the field is in DATE9. format.
For DATE9, correctly exported values of 01/01/1959 in the SAS dataset,
become '1-Jan-59' in the exported .XLSX file.
Is this is known issue, or is there a workaround for it?
For all records where the value of the date field in the original file is <= 12/31/1959, then the value of that field in the exported XLSX file, is 12/31/1959.
I am not experiencing this. I get the expected output in Excel.
data a;
date='02JAN1929'd;
format date ddmmyy10.;
run;
proc export data=a outfile="test.xlsx" dbms=excel replace;
run;
Looks like you might be getting this problem: 58526 - The ddmmyy10. format writes different values to different sheets when you are using the XLSX...
What SAS maintenance release are you using?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.