topic Re: Exporting to "excel" via ODS in ODS and Base Reporting

Exporting to "excel" via ODS

Bill — Mon, 13 Jul 2015 20:11:39 GMT

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

Re: Exporting to "excel" via ODS

Reeza — Mon, 13 Jul 2015 20:13:03 GMT

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

Re: Exporting to "excel" via ODS

Bill — Mon, 13 Jul 2015 20:19:57 GMT

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

Re: Exporting to "excel" via ODS

Cynthia_sas — Mon, 13 Jul 2015 20:47:42 GMT

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

Re: Exporting to "excel" via ODS

Reeza — Mon, 13 Jul 2015 21:17:11 GMT

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

Re: Exporting to "excel" via ODS

Bill — Tue, 14 Jul 2015 13:18:24 GMT

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

Re: Exporting to "excel" via ODS

RW9 — Tue, 14 Jul 2015 14:28:03 GMT

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;

Re: Exporting to "excel" via ODS

Bill — Tue, 14 Jul 2015 18:58:08 GMT

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

Re: Exporting to "excel" via ODS

RW9 — Wed, 15 Jul 2015 09:19:28 GMT

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...