68654 - Using a FILENAME fileref in PROC EXPORT with DBMS=XLSX overwrites the existing sheet

Problem Note 68654: Using a FILENAME fileref in PROC EXPORT with DBMS=XLSX overwrites the existing sheet

If you use a FILENAME fileref in the OUTFILE= portion of a PROC EXPORT statement that uses DBMS=XLSX in SAS^® 9.4M7 (TS1M7), the existing sheet is overwritten. This issue occurs even if a sheet name is specified in the code. In previous releases of SAS, when you used a fileref in OUTFILE=, the engine would export to multiple sheets within the same Excel (.xlsx) file.

A workaround is to use the full path including the file name in OUTFILE= instead of a fileref or to use a macro variable in OUTFILE= .

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

Product Family	Product	System	Product Release		SAS Release
Product Family	Product	System	Reported	Fixed*	Reported	Fixed*
SAS System	Base SAS	Microsoft® Windows® for x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows 8 Enterprise 32-bit	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows 8 Enterprise x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows 8 Pro 32-bit	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows 8 Pro x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows 8.1 Enterprise 32-bit	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows 8.1 Enterprise x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows 8.1 Pro 32-bit	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows 8.1 Pro x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows 10	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows Server 2008	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows Server 2008 R2	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows Server 2008 for x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows Server 2012 Datacenter	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows Server 2012 R2 Datacenter	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows Server 2012 R2 Std	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows Server 2012 Std	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows Server 2016	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows Server 2019	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Windows 7 Enterprise 32 bit	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Windows 7 Enterprise x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Windows 7 Home Premium 32 bit	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Windows 7 Home Premium x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Windows 7 Professional 32 bit	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Windows 7 Professional x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Windows 7 Ultimate 32 bit	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Windows 7 Ultimate x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		64-bit Enabled AIX	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		64-bit Enabled Solaris	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		HP-UX IPF	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Linux for x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Solaris for x64	9.4_M7	9.4_M8	9.4 TS1M7	9.4 TS1M8
		Microsoft Windows Server 2022	9.4_M8		9.4 TS1M8
		Microsoft Windows 11	9.4_M8		9.4 TS1M8
		z/OS 64-bit	9.4_M8		9.4 TS1M8
		z/OS	9.4_M8		9.4 TS1M8

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

This code can be used to reproduce the export problem. When the .xlsx file is viewed, only Sheet "2" exists because it overwrote the Sheet "1" that existed previously. Note you have to change the FILENAME to point to an appropriate location for your system.

data x;
y=1;
run;

data x2;
y2=1;
run;

filename H "C:\tmp\filename.xlsx";

proc export
data=x
dbms=xlsx
outfile=H
replace;
sheet="1";
run;

proc export
data=x2
dbms=xlsx
outfile=H
replace;
sheet="2";
run;

Type:	Problem Note
Priority:	high

Date Modified:	2022-01-26 09:05:45
Date Created:	2021-12-01 16:41:41

Support

Problem Note 68654: Using a FILENAME fileref in PROC EXPORT with DBMS=XLSX overwrites the existing sheet

Operating System and Release Information