topic Appending a sheet to an already existing Excel document with ODS in ODS and Base Reporting

Appending a sheet to an already existing Excel document with ODS

LB — Wed, 01 Feb 2017 00:05:03 GMT

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.

Re: Appending a sheet to an already existing Excel document with ODS

Sven111 — Wed, 01 Feb 2017 00:25:40 GMT

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.

Re: Appending a sheet to an already existing Excel document with ODS

SASKiwi — Wed, 01 Feb 2017 00:25:22 GMT

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.

Re: Appending a sheet to an already existing Excel document with ODS

ballardw — Wed, 01 Feb 2017 00:33:08 GMT

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.

Re: Appending a sheet to an already existing Excel document with ODS

Reeza — Wed, 01 Feb 2017 02:17:07 GMT

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

Re: Appending a sheet to an already existing Excel document with ODS

RW9 — Wed, 01 Feb 2017 09:44:43 GMT

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

Re: Appending a sheet to an already existing Excel document with ODS

LB — Wed, 01 Feb 2017 18:11:54 GMT

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

Re: Appending a sheet to an already existing Excel document with ODS

JimRibble — Wed, 27 Jun 2018 15:10:53 GMT

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.

Re: Appending a sheet to an already existing Excel document with ODS

Reeza — Wed, 27 Jun 2018 21:11:27 GMT

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

Re: Appending a sheet to an already existing Excel document with ODS

RW9 — Thu, 28 Jun 2018 08:18:03 GMT

Just going to paraphrase a bit there:

decommissioning + security flaw != renabled