The SAS Output Delivery System and reporting techniques

Exporting to "excel" via ODS

Reply
Super Contributor
Posts: 292

Exporting to "excel" via ODS

I'm looking for an ods method to export data to a file that excel (2007 or newer) can read.

I need to be able to place data in multiple sheets within the file and do so on different days.

Looked at

ods tagsets.excelxp file='c:\temp\namesheets.xls' options(sheet_name='Koala');

proc print;

run;

ods _all_ close;

ods tagsets.excelxp file='c:\temp\namesheets.xls' options(sheet_name='Grizzly');

proc print;

run;

ods _all_ close;

but the second file overwrites the first.

Are there other options that I can try?

Thank you,

Bill

Super User
Posts: 19,855

Re: Exporting to "excel" via ODS

What version of Excel? If it's proc print you may just need proc export.

Super Contributor
Posts: 292

Re: Exporting to "excel" via ODS

Excel 2007 or later. Proc Export won't do.

SAS Super FREQ
Posts: 8,868

Re: Exporting to "excel" via ODS

Hi:

  Any ODS technique works by re-creating the file referenced in the FILE= option. So if you make FILE1.XML on day 1 with 2 sheets and you come back a week later and run the same code over again to write to FILE1.XML, then the file will be over-written a week later, as you have discovered. ODS does not "add" a sheet to an existing file. For this reason, people frequently keep their data in SAS-accessible files and when they need to add a sheet, they recreate the entire workbook again.

  Otherwise, you might consider making a "base" Worksheet, and then having SAS make the individual files and then you have an Excel macro or VB Script that "collects" all the individual files into the "base" or constantly adds the newest file as a sheet to the base file. This would be outside of the ODS process, however.

cynthia

Super User
Posts: 19,855

Re: Exporting to "excel" via ODS

Do you need formatting in the file or something else that ODS delivers?

Super Contributor
Posts: 292

Re: Exporting to "excel" via ODS

Reeza, indeed, I do need a number of the ODS customizations

Super User
Super User
Posts: 7,984

Re: Exporting to "excel" via ODS

The reason the second statement overwrites the first is because you have two file statements.  Simply change to this:

ods tagsets.excelxp file='c:\temp\namesheets.xls' options(sheet_name='Koala');

proc print;

run;

ods _all_ close;

ods tagsets.excelxp options(sheet_name='Grizzly');

proc print;

run;

ods _all_ close;

Super Contributor
Posts: 292

Re: Exporting to "excel" via ODS

RW9, yes, that works, but not if I run Koala today and want to add Grizzly tomorrow.

Super User
Super User
Posts: 7,984

Re: Exporting to "excel" via ODS

I would then suggest that you need to re-think your approach.  Excel is not a <insert whatever task you can think of> tool.  Whilst you could do this, i.e. have a base Excel file, then each day you export your SAS data to CSV, and that base Excel file opens the CSV data and posts it into the base file (using VBA), I can't recommend this approach.  Excel is limited in many ways, one being the number of tabs which you will hit pretty quickly.  Secondly will anyone actually ever look at the data.  This is my biggest gripe.  So much data gets dumped into Excel which no-one ever looks at.  Is this for data transfer purposes, i.e. will someone else have to import and work with the data, then use a proper data transfer format (CSV, XML etc.).  If this is a report, do people really want to see thousands of rows of data on numerous sheets, I personally wouldn't look at it.  Consider summarising, grouping, graphing, or somehow reflecting the data in a easy to read format, then people might take notice of it.  If its just a dump, then proc report it to a PDF file and be done with it.  You will note that for the three options, Excel is not mentioned...

Ask a Question
Discussion stats
  • 8 replies
  • 754 views
  • 3 likes
  • 4 in conversation