PROC EXPORT ignoring Replace option

Question

I have a SAS program which exports several tables to an Excel workbook. If the sheet specified in my PROC EXPORT does not exist in the Excel file, then a new sheet is created. However, if the sheet already exists, then nothing happens (although the SAS log tells me that the file was "successfully created"). It does not overwrite the existing data or create a new sheet.

I am using SAS 9.4 and exporting to Excel 2010.

proc export data=my.thing
 outfile= "C:\Folder1\Folder2\myExcelFile.xlsx" 
 dbms=xlsx replace; 
 label; 
 sheet='thing'; 
 run;

Have you checked your libname statement and outfile= path to ensure correctness? — Yick Leung
Yes, it is all correct. Using the example above, if I change the sheet name to 'newthing' then it creates a tab named newthing with the correct data. I only have a problem when trying to replace a sheet and/or named range. — Oliver

Yick Leung Yick Leung · Accepted Answer · 2013-12-30T14:14:23

Your proc export statement looks okay. Perhaps the problem is when you're trying to overwrite the existing excel file. When you try to replace them, the existing excel file may not be in the expected format as the exporting excel file. Hence, there is trouble overwriting it. Look at the width of the variables in your existing excel and compare it with the new sheet "thing", maybe you can spot some inconsistencies.

PROC EXPORT ignoring Replace option

1 Answers