EXCEL EXPORT

[Woop122]

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;

[Reeza]

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+

[Woop122]

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. 

[RW9]

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).