topic Re: EXCEL EXPORT in SAS Procedures

EXCEL EXPORT

Woop122 — Tue, 27 Jun 2017 23:45:14 GMT

Hi,

I keep getting errors w/ excel export and was wondering if anyone knows an effective workaround. If I run these statements and then run it again right away, it says cannot replace sheet because it has existing formulas that reference other pages. I'm working off of a brand new excel w/o any formulas, so I'm not sure why this error occurs. I couldn't use the named range option w/ libnames since I'm accessing sas remotely.

PROC EXPORT
DATA=NON_VIRAL_DS
DBMS=XLSX
OUTFILE = "D:\UserData\chinht\CMV Trend Export.xlsx"
REPLACE;
SHEET = 'DS NO VIRAL';
RUN;

PROC EXPORT
DATA=NON_CMV_VIRAL_DS
DBMS=XLSX
OUTFILE = "D:\UserData\chinht\CMV Trend Export.xlsx"
REPLACE;
SHEET = 'DS NO CMV';
RUN;

PROC EXPORT
DATA=CMV_VIRAL_DS
DBMS=XLSX
OUTFILE = "D:\UserData\chinht\CMV Trend Export.xlsx"
REPLACE;
SHEET = 'DS CMV';
RUN;

Re: EXCEL EXPORT

Reeza — Wed, 28 Jun 2017 01:31:37 GMT

What version of SAS are you using? I don't believe DBMS = XLSX supported multisheet files right away, it wasn't until a later implemenation. It's definitely available as of SAS 9.4+

Re: EXCEL EXPORT

RW9 — Wed, 28 Jun 2017 08:48:49 GMT

Not a good idea to code all in capitals, hard to read. Now as this code runs fine on my 9.4 machine I suspect you have an earlier version of SAS. Thus you may have to either proc export to XLS, or use tagsets.excelxp and proc report to generate an XML file which can be read by Excel. Personally, unless the data is a straight data-dump (and if so you would be better off using a good data transfer file format like CSV) then using one of the methods to create excel files with formatting options is preferred (such as tagsets.excelxp).

Re: EXCEL EXPORT

Woop122 — Wed, 28 Jun 2017 15:19:07 GMT

I am on sas 9.4 It can export the first time without issues, but if I re-run the exports right after it will not run. I need to be able to update my sheets if there are changes made.