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

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

Lazygeek — Tue, 01 Oct 2019 11:12:40 GMT

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

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

Reeza — Tue, 01 Oct 2019 16:12:28 GMT

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

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

ChrisNZ — Tue, 01 Oct 2019 21:08:05 GMT

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.

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

Lazygeek — Fri, 08 Nov 2019 07:48:52 GMT

Hi Reeza,

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

Thanks

LzG

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

Lazygeek — Fri, 08 Nov 2019 07:47:49 GMT

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 .