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?
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.