BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I'm using the following code to output to Excel; but I'm noticing that it doesn't REPLACE old data with new. What would be a better solution if I'm constantly updating data with new data that I want written out to same excel file and worksheet. Thanks

OPTION PAGENO=1;
libname myxls "U:\QEP\Revenue\Revenue_Statistics\DSH_DATA\RevStats.xls";
data myxls.check;
set b2;
libname myxls clear;
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
What's unclear from your post is whether you are getting any type of SAS error in your SAS-generated log output. This would be useful to learn. Also, if there is some error, I recommend you reply to your post with a COPY/PASTE snapshot from the error point leading up to the error condition. Lastly, are you able to use this same code to generate a new Excel document -- again something useful for your own (as well as the forum) debugging response/input.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
Here is the error message:


NOTE: Libref MYXLS was successfully assigned as follows:
Engine: EXCEL
Physical Name: U:\QEP\Revenue\Revenue_Statistics\DSH_DATA\RevStats.xls

ERROR: The MS Excel table check has been opened for OUTPUT. This table already exists, or there is a name conflict with an existing object. This
table will not be replaced. This engine does not support the REPLACE option.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 3:03.54
cpu time 0.11 seconds



659 data myxls.check;
660 set b2;
661 libname myxls clear;
NOTE: Libref MYXLS has been deassigned.
deleted_user
Not applicable
Here is another error message I received when I update the data and try to output updated data to the existing excel file and spreadsheet.

855
856 options pageno=1;
857 libname myxls "U:\QEP\Revenue\Revenue_Statistics\DSH_DATA\RevStats.xls";
NOTE: Libref MYXLS was successfully assigned as follows:
Engine: EXCEL
Physical Name: U:\QEP\Revenue\Revenue_Statistics\DSH_DATA\RevStats.xls
858 data myxls.RevStats;
859 set b2;
860 libname myxls clear;
NOTE: Libref MYXLS has been deassigned.
861

ERROR: Libname MYXLS is not assigned.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
With your last error log, you need to code a RUN; statement before the LIBNAME.

The SAS support http://support.sas.com/ website has SAS-hosted DOC and supplemental technical and conference papers - available using either the website SEARCH facility or Google - here is one I found which discusses REPLACE not being an option, as well as showing applicable examples:

http://www2.sas.com/proceedings/sugi31/024-31.pdf


Scott Barry
SBBWorks, Inc.
WendyT
Pyrite | Level 9
Other options to consider might be PROC EXPORT with the REPLACE option, or using the X statement to delete the existing file from your operating system.

PROC EXPORT
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a000316287.htm

X statement
http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a000212624.htm

Wendy

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
  • 5 replies
  • 1375 views
  • 0 likes
  • 3 in conversation