Date:         Tue, 11 Jan 2011 17:17:10 -0500
Reply-To:     Lorne Klassen <lk1@ROGERS.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Lorne Klassen <lk1@ROGERS.COM>
Subject:      Re: Proc Export difference between dbms=xls and dbms=excel

I submitted this to a 64-bit server running SAS 9.2:

rsubmit;
   proc export data=sashelp.air outfile="c:\Index.xls" dbms=xls replace;
      sheet='SheetABC';
   run;
endrsubmit;

It successfully created the workbook c:\Index.xls which didn't exist prior.

Then I ran this:

rsubmit;
   proc export data=sashelp.air outfile="c:\Index.xls" dbms=xls replace;
      sheet='SheetDEF';
   run;
endrsubmit;

It created the sheet SheetDEF in the same workbook (c:\Index.xls).
SheetABC was still there as expected.

So it worked fine for me. I did add quotes around the Excel file name
which you didn't show.

DBMS=XLS is a good choice.

-DBMS=XLS works better than DBMS=Excel in my opinion (esp. for Import).
-For Export, if the Excel file already exists it will create a .bak file
automatically. This can't be controlled but it may be considered a good
thing.
-DBMS=XLS works on 64-bit machines, unlike DBMS=Excel.
-Only downside to DBMS=XLS is that it is not compatible with Excel 2007 so
it creates an Excel 97-2003 format file. It also cannot import Excel 2007
files.


On Tue, 11 Jan 2011 14:55:06 -0500, Diana Grundmann
<sasstuff2000@YAHOO.DE> wrote:

>Hi,
>
>at the moment I am working with SAS 9.1.3 on Windows.
>
>I use following code to create/append an excel file.
>
>proc export data=_outputds outfile=Index.xls dbms=excel replace;
>sheet='Sheet Name'; run;
>
>If I run this code the sheet will be created in the existing workbook. But
>if I change the code to
>
>proc export data=_outputds outfile=Index.xls dbms=xls replace;
>sheet='Sheet Name'; run;
>
>than I get the error message: ERROR 180-322 Statement is not valid or ist
>is used out of proper order for the sheet statement and my Excel workbook
>is overwritten and contains only one sheet with name 'Sheet1'.
>
>We are planning to switch to SAS9.2 on a 64-bit system thus I cannot use
>the EXCEL libname statement and I have to create my program the way that
>it run on both SAS9.1.3 and SAS9.2. If I understand the usage note 33228
>from SAS correctly I can use the dbms=xls options with the sheet
>statement.
>
>Has someone experiences with this on SAS9.2 on a 64-bit system?
>
>Thanks for help.
>Diana