sas, Can't overwrite Excel spreadsheet using PROC EXPORT

comp.soft-sys.sas - The SAS statistics package.


sas >> Can't overwrite Excel spreadsheet using PROC EXPORT

by Gary » Fri, 02 Oct 2009 08:31:14 GMT

Hi all,

I have the following code which exports two datasets to separate
spreadsheets within the same workbook (excel file). However, I cannot
seem to get SAS to overwrite the existing sheet with the new data even
using the REPLACE option.

Here's my code:
----------
proc export data=a
outfile="&xl_out." replace;
sheet="SUMM1";
run;

proc export data=b
outfile="&xl_out." replace;
sheet="SUMM2";
run;
----------

My macro, &xl_out, simply defines the Excel file to export to.

The log reports no errors. When I open the workbook, I find two new
sheets, SUMM11 and SUMM21, created instead of dumping the new data
into the preexisting sheets, SUMM1 and SUMM2.

I cannot delete the existing SUMM1 and SUMM2 spreadsheets, as I have
formulas in other sheets that are linked to those two sheets.
Deleting the sheets will return the dreaded #REF! error.

Any ideas?

Thanks,
Gary

Top

sas >> Can't overwrite Excel spreadsheet using PROC EXPORT

by data _null_ » Fri, 02 Oct 2009 19:27:08 GMT





Here is the note that explains the results you are getting.
http://support.sas.com/kb/20/923.html

But I don't know how you can fix it. Maybe if you delete the named
ranges you will get the correct result.


Top

sas >> Can't overwrite Excel spreadsheet using PROC EXPORT

by iebupdte » Fri, 02 Oct 2009 21:34:47 GMT




I've been trying to test this further and I believe you want to create
ranges not delete. But I don't have example or details.

Top

Can't overwrite Excel spreadsheet using PROC EXPORT

by art297 » Mon, 05 Oct 2009 02:46:59 GMT

Gary,

I, too, have had proc export exhibit unwanted behavior, including what you
had mentioned (and what data_null_ had explained), as well as literally
destroying a worksheet's contents.

Interestingly, in testing it since your post, I discovered that one doesn't
seem to run into the problem when the ORIGINAL spreadsheet is one that was
created by proc export. Apparently, SAS assigns the named range at that
time, and that is all that is needed to re-export data to it.

I created a workbook (test.xls) with a sheet name labeled 'data1' via proc
export.

Then, I opened that workbook in Excel, created a new sheet labeled
'calculations', which contained formulas summing the values from 'data1'.
After creating the new worksheet, I saved the file and closed excel.

Then, I exported new data to test.xls, sheet="data1" and, surprisingly,
data1 contained the new data and calculations contained the correct, updated
summaries.

HTH,
Art
--------



Top
Similar Threads

1. using proc export/dbms=xls and excel lost date format 

I think it's very weird. And it's very easy to reproduce the problem.

SAS 9.2 (32-bit/64-bit)

proc export data=whatever_dataset_with_datetime;
outfile='path\filename'
dmbs=xls replace;
sheet='01';
run;

Note: the reason I am using "dbms=xls" instead of "dbms=excel" is xls
is only option in 64-bit sas. This "XLS" causes the following problem.

After you submit it, everything is fine.

OPEN the excel and SWITCH two columns, THEN SAVE it.

run the same code again but with sheet='02', open the excel file, you
will find the date format in sheet02 is LOST.

Do you know what's the fix?

Thanks

2. reading excel spreadsheets using proc import

3. PROC EXPORT error "DBMS type EXCEL2000 not valid for export" 

Hi all:

  I tried to export a SAS dataset to EXCEL and got some error message:

1606  proc export
1607    data=BackDateInfo
1608    outfile="D:\BackDateInfo.XLS"
1609    DBMS=EXCEL2000
1610    replace;
ERROR: DBMS type EXCEL2000 not valid for export.

  How do I fix it?  I am using Excel Professional Edition 2003.

  Thanks!

4. DTS package to execute stored proc and export data into Excel - SQL Server DTS

5. SAS 9.1, Proc Export and Excel question 

Hello all,
We just got SAS 9.1 installed and I was happy to see that PROC EXPORT 
had a SHEET = option so that we can easily export to a specific sheet in 
a workbook. With my group, that is only half the battle.

Is there a way to export to a specific cell in a workbook without using 
DDE commands (we are using DDE now)? Our office is using Excel XP and 
unlike Office 2003 Professional, it does not have the easy XML drag and 
drop option.

Am I missing something obvious or is DDE our only option at this time?


Thanks so much

Paula Sims
 XXXX@XXXXX.COM

6. Proc Export Excel ERROR: Error trying to establish connection: Class not registered

7. proc export to excel 

Hi everybody,

Is there a way I can export a dataset to an excel spreadsheet without the
column names?

Thank you.


Jeri

8. How to specify the order of fields in PROC EXPORT to and Excel