BookmarkSubscribeRSS Feed
Woop122
Calcite | Level 5

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;

3 REPLIES 3
Reeza
Super User

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1173 views
  • 0 likes
  • 3 in conversation