SAS Export to Excel corrupts formulae in Excel

Adam5 — Tue, 22 Jan 2019 15:38:54 GMT

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

Re: SAS Export to Excel corrupts formulae in Excel

andreas_lds — Tue, 22 Jan 2019 15:51:05 GMT

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

Re: SAS Export to Excel corrupts formulae in Excel

Reeza — Tue, 22 Jan 2019 16:17:13 GMT

Try using XLSX DBMS instead?

topic Re: SAS Export to Excel corrupts formulae in Excel in New SAS User

SAS Export to Excel corrupts formulae in Excel

Re: SAS Export to Excel corrupts formulae in Excel

Re: SAS Export to Excel corrupts formulae in Excel