topic Re: SAS 9.4 PROC EXPORT XLSX in SAS Programming

SAS 9.4 PROC EXPORT XLSX

SAS_MAN_MN — Mon, 12 Sep 2022 19:07:52 GMT

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?

Re: SAS 9.4 PROC EXPORT XLSX

PaigeMiller — Mon, 12 Sep 2022 19:24:24 GMT

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;

Re: SAS 9.4 PROC EXPORT XLSX

SASKiwi — Mon, 12 Sep 2022 20:09:53 GMT

Looks like you might be getting this problem: 58526 - The ddmmyy10. format writes different values to different sheets when you are using the XLSX driver to export to EXCEL (sas.com)

What SAS maintenance release are you using?