The SAS Output Delivery System and reporting techniques

Appending a sheet to an already existing Excel document with ODS

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 139
Accepted Solution

Appending a sheet to an already existing Excel document with ODS

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. 

 

 

 

 

 


Accepted Solutions
Solution
‎02-01-2017 01:09 PM
Frequent Contributor
Posts: 83

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

[ Edited ]

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.

 

 

View solution in original post


All Replies
Solution
‎02-01-2017 01:09 PM
Frequent Contributor
Posts: 83

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

[ Edited ]

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.

 

 

Super User
Posts: 19,802

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

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

Super User
Posts: 3,254

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

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.  

Super User
Posts: 11,343

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

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.

Super User
Super User
Posts: 7,955

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

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

Frequent Contributor
Frequent Contributor
Posts: 139

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

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 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 2182 views
  • 0 likes
  • 6 in conversation