Date:         Wed, 24 Aug 2011 11:10:50 -0400
Reply-To:     "Durrett, Cindy" <CindyDurrett@FDLE.STATE.FL.US>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Durrett, Cindy" <CindyDurrett@FDLE.STATE.FL.US>
Subject:      FW: [SAS-L] Export to Excel Spreadsheet
Content-Type: text/plain; charset="us-ascii"

I sent this in response directly to the OP but thought I'd post it here, too, in case there's someone else like me who gleans things from the replies!

Cindy D.
FDLE / FSAC / CIB


-----Original Message-----
From: Durrett, Cindy
Sent: Wednesday, August 24, 2011 7:56 AM
To: 'SUBSCRIBE SAS-L Olivier Van Parys'
Subject: RE: [SAS-L] Export to Excel Spreadsheet

We don't have the components that let us go directly to Excel so we will ODS CSV data and then open it up with Excel, but that only goes to one sheet.  To get data to multiple sheets, here's what we do:

To output onto multiple worksheets in Excel, use this code:
ODS LISTING CLOSE;
ODS TAGSETS.EXCELXP FILE='\\path\whereyouwantit\nameoffile.XML';
*** here's where you put proc print or proc freq or whatever. Remember to subset if you're output will be more rows that Excel can handle.
ODS TAGSETS.EXCELXP CLOSE;
ODS LISTING;

And then we open the XML file in Excel and format appropriately.  I made a macro in Excel so whenever I open an XML file that I generate with the above code, it will apply the basic formatting I want (fonts, colors, etc.).

This is just another example of how I wish we could afford to buy all the parts of SAS that we would actually use.

Hope this helps or at least points you in the right direction.  We started using this with 9.1 and still use it with 9.2.


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of SUBSCRIBE SAS-L Olivier Van Parys
Sent: Wednesday, August 24, 2011 5:02 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: [SAS-L] Export to Excel Spreadsheet

Hi Everyone,

I am trying to export a few tables into excel but I would like each of these
table to go into specific sheets into the same .xls document. I have tried the
following syntax without any success (ERROR: DBMS type EXCEL not valid for
export). How should I proceed?


PROC EXPORT DATA = work.ventes
OUTFILE = "c:\temp\excel.xls"
DBMS = EXCEL
REPLACE ;
SHEET = "ventes" ;
RUN ;