topic Re: Output to Excel in SAS Procedures

Output to Excel

deleted_user — Mon, 10 Aug 2009 14:51:42 GMT

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;

Re: Output to Excel

sbb — Mon, 10 Aug 2009 16:41:27 GMT

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.

Re: Output to Excel

deleted_user — Mon, 10 Aug 2009 18:31:56 GMT

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.

Re: Output to Excel

deleted_user — Mon, 10 Aug 2009 19:09:18 GMT

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

Re: Output to Excel

sbb — Mon, 10 Aug 2009 19:33:22 GMT

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.

Re: Output to Excel

WendyT — Mon, 10 Aug 2009 20:02:25 GMT

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