BookmarkSubscribeRSS Feed
Bill
Quartz | Level 8

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

8 REPLIES 8
Reeza
Super User

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

Bill
Quartz | Level 8

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

Cynthia_sas
SAS Super FREQ

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

Reeza
Super User

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

Bill
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Bill
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2128 views
  • 3 likes
  • 4 in conversation