Appending a sheet to an already existing Excel document with ODS

[LB]

Hello all- 

So I have an excel document that is processed somewhere else, scooped by SAMBA, and reclocated to an Unix drive- 

What I am looking to do is add an excel sheet to that using proc report- 

If I attempt to do something like- 

ods excel file="/apps/sas/datasets/data137/xxx/dev/xxx/data/xxx/xxx_REPORT.xlsx" style=Seaside options(sheet_name="MRN LEVEL DATA" SHEET_INTERVAL= 'PAGE' autofilter='1' FROZEN_ROWHEADERS='no'
GRIDLINES='ON' embedded_titles='yes' embedded_footnotes='yes');

...proc report code...

ods _all_ close 

It  simply wipes out all the previous sheets- 

Any suggestions welcomed. 

Thank you. 

[Sven111 · Accepted solution]

I had a similar problem, unfortunately I don't have good news for you, this isn't possible using ODS EXCEL (unless they've made changes in the latest M4 release).  Whenever you open a file with "ODS EXCEL FILE=...." it'll kill anything that's already there, so you can't add some data to it, close ODS and then add more later.  What I ended up doing was using PROC EXPORT with DBMS = XLSX like this:

PROC EXPORT DATA=LIB_1.Dataset_1
            DBMS=XLSX
            OUTFILE="home/username/file.xlsx"
            REPLACE;
    SHEET="Tab Name";
RUN;

This will allow you to add a sheet to an existing Excel file and not clear everything else out.   You can do this after the file has been created using ODS EXCEL to add the sheet, or I ended up just using PROC EXPORT for everything, although it doesn't have all of the output options that ODS EXCEL has unfortunately.

[Reeza]

If you have access to Add In for Microsoft Office that could be another option. 

[SASKiwi]

Using ODS requires that you build your Excel file from scratch. You cannot update an existing spreadsheet.

One option would be to import the data from your existing spreadsheet, append your new data then write it out completely using ODS.

Other options include PROC EXPORT or the EXCEL / XLSX LIBNAME. You would need SAS/ACCESS to PC Files installed and licensed for that. You have the ability to replace or add sheets in your Excel workbook with these.  

[ballardw]

Or create the Excel output and then insert into Excel using Excel tools. If you are generating many sheets from SAS then generate ALL of the SAS output into a single ODS Excel output file and then insert/combine whatever you want to call it.

[RW9]

The simplest way I have found is to reverse the thinking.  You have an Excel file as the base, then export your data from SAS (proc report, export etc. doesn't matter, just a format that Excel can read).  Then have a macro - doesn't even have to be in the base file - which opesn your SAS output, copies over the data to where it needs to go, and saves the file.  Its really very simple, can be done in a couple of lines of VBA code if its just copying a sheet across (note this code is just typed in to give you an idea):

Sub Sheet_copy ()

  Application.Open "...\your_export file.xlsx"

  Workbook("your_export file.xlsx").Sheets("Sheet1").MoveTo(Workbook("Base.xlsx",After)

  Workbook("your_export file.xlsx).Close

  Workbook("Base.xlsx").Save

End Sub

[LB]

Thanks for all your solutions-I thought the proc export as the runner up but I was seeing if there a way otherwise as I like the format controls, etc etc that proc report provides. Oh well.  

VBA is out of the question for multiple reasons as Microft Add-ins.  

Lawrence 

[JimRibble]

It's very old school but you can update specific cells and perform other operations on an existing Excel workbook from SAS using a DDE connection.  There are some environmental and scripting issues involved here but if things are set up right and you do some research this method is still available and can be used.

[Reeza]

Microsoft officially started decommissioning DDE in Dec 2017 due to a security flaw. You may need to get DDE renabled by your IT area.

[RW9]

Just going to paraphrase a bit there:

decommissioning + security flaw != renabled