BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LB
Quartz | Level 8 LB
Quartz | Level 8

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. 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Sven111
Pyrite | Level 9

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

9 REPLIES 9
Sven111
Pyrite | Level 9

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
Super User

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

SASKiwi
PROC Star

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8 LB
Quartz | Level 8

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
Calcite | Level 5

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

Just going to paraphrase a bit there:

decommissioning + security flaw != renabled

Smiley Wink

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
  • 9 replies
  • 30578 views
  • 2 likes
  • 7 in conversation