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
What version of Excel? If it's proc print you may just need proc export.
Excel 2007 or later. Proc Export won't do.
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
Do you need formatting in the file or something else that ODS delivers?
Reeza, indeed, I do need a number of the ODS customizations
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;
RW9, yes, that works, but not if I run Koala today and want to add Grizzly tomorrow.
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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.