proc export error : Can not replace sheet because it has existing formulas that reference other page

[Lazygeek]

Hi , 

I am trying to export few datasets to different sheets of a excel file with the below code . It works fine but for one sheet it throws  below error. If i delete that sheet and run again it works fine. But i cannot delete that sheet because there are many other graphs that reference from the sheet. Any help is greatly appreciated. I am using SAS 9.4

proc export data=stattrans
outfile= "&OUTDIR.\&loan_type._&product._report.xlsx" dbms=excel2007 replace;
SHEET="DATA_&testname.stat";
run;

error : Can not replace sheet because it has existing formulas that reference other page

[Reeza · Accepted solution]

Does the sheet being replaced have formulas?
Have you tried using a named range instead? Or a different DBMS?

[Lazygeek]

Hi Reeza,

 Sorry for the late response. Changing DBMS worked. Your help is much appreciated. 🙂

Thanks

LzG

[ChrisNZ]

I prefer to have the data sheets separate from the report sheets to avoid this kind of issue and to keep things clean and simple.

If that's not an option, a named range might be another way as mentioned.

[Lazygeek]

Hi Chris, in this case changing the DBMS worked for me. But your suggestion for using named ranges helped me to solve other issue. Kudos !! Thanks .