SAS Export to Excel corrupts formulae in Excel

[Adam5]

Hi

I have been running a process for a few years now where I carry  out a load of data extraction and manipulation in SAS 9.4, which at the end of the process gets exported into an Excel file using PROC export. The excel file then manipulates and presents the data for the end user.

The (intermitant) problem that I have, is that sometimes once the SAS code has finished (seemingly without any problems) when I go into the Excel workbook many of the formulae have been corrupted. What seems to be happening is that the SAS export somehow changes some of the range names within formulas that I have on sheets where SAS shouldn't be touching anything.

PROC EXPORT DATA= dmi.Nb_summary

OUTFILE= " &path.&File_Name."

DBMS=EXCEL LABEL REPLACE;

SHEET="NewBusData";

RUN;

For example - on one sheet before the SAS export runs - the formula in a cell may refer to a particular range name .e.g. =Topexp!A5

Then I run the SAS routine - reopen the excel and the same cell now says =Botexp!A5. It's driving me crazy. I can go around and fix it all, then it runs fine for a few days and then it goes wrong again.

Any ideas?

Thanks in advance

Adam

[andreas_lds]

Have you checked if any hotfix related to proc export and excel is available for your installation?

[Reeza]

Try using XLSX DBMS instead?