BookmarkSubscribeRSS Feed
mrom34
Calcite | Level 5

Hi,

 

I used to be able to run the PROC EXPORT code below without any issues but now SAS gives me an error. I suspect it is because IT just installed a maintenance release. Do you think it is the reason and is there an option to avoid this error?

 

proc export data=final
outfile="\\fileshare\users\rm\example5.xlsx"
dbms=xlsx replace ;
sheet="Data_import";
run;

 

 

I Get the error message:

Can not replace sheet because it has existing formulas that reference other pages
ERROR: Export unsuccessful. See SAS Log for details.

6 REPLIES 6
mohamed_zaki
Barite | Level 11

This error is mentioned in in "SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition" EXPORT Procedure

 

"If you use the EXPORT procedure with DBMS=XLSX and the page has formulas that reference other pages, an error message is written to the log. The error states that the page cannot be replaced because it has formulas that reference other pages."

stephglo
Calcite | Level 5

I have seen this error,

"Can not replace sheet because it has existing formulas that reference other pages",

even in cases where the sheet did not contain any formulas. We are currently experiencing problems with Proc Export corrupting Excel files. My understanding was that this was supposed to have been fixed in 7.12 HF2 but we are still seeing issues. It would be really great if someone were to include a link to that support issue because I'm not finding anything about it on this site. I can only conclude that I'm not looking in the right places because our issue is replicable.

PavelD
Obsidian | Level 7

Hi, is there any progress on this? I keep getting the error even when there are clearly NO formulas in the sheet into which the data are exported (but obviously there are formulas in other sheets which link to these data, that is the whole point of using an Excel template).

 

EDIT: It seems the workaround for me is to export data to CSV, and then use Excel connections feature, which link certain ranges in an Excel workbook with external CSVs. The advantage of this method is that you may link ranges anywhere in the given sheet, that means that you can have charts/formulas on the same sheet as your imported data, which is not possible with the SAS xlsx proc export.

SASKiwi
Opal | Level 21

What SAS 9.4 maintenance release are you on? It is supposed to be fixed in 9.4 M4.

PavelD
Obsidian | Level 7
We have 9.4.3.0. I am not sure whether the version mentioned by you (9.4 M4) is more or less recent? Anyway, the "Excel connections" workaround works well for the moment and for my needs. Regarding the hot fix, it cannot be installed on our system now, I don't know why. Thanks Kiwi
SASKiwi
Opal | Level 21

Looks like this SAS note is relevant:

 

http://support.sas.com/kb/58/890.html

 

You might want to check Maintenance 4 (SAS 9.4 TS1M4) when it is released in the next week or so to see if this is now fixed.

 

A workaround would be to replace formulas with values using Paste Special.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3618 views
  • 2 likes
  • 5 in conversation