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?
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.