How do I add another worksheet to an existing workbook?

[kyle234]

I am running a macro that creates two data sets, I want to output 2 datasets to the same excel worksheet. Then I want to call the macro again and add another worksheet with two datasets to the same workbook. Whenever I run this code, the second run does not add another worksheet. I am wondering what is going on. Thank you

ods excel file='\path'
options( sheet_interval='NONE' sheet_name="&SCHEMA");
proc report data=TABLE_DIFF_FINAL;
run;

proc report data=VAR_DIFF_FINAL;
run;
ods excel close;

[ballardw]

Reports are not "data sets", so you are not exporting anything.

When doing things like placing reports into a single spreadsheet file  then all of the reports must occur between one ODS Excel file= / ods excel close; "sandwich";

So remove the ODS EXCEL FILE= part from the macro. You don't show how the "macro" is called so I am guessing but dummy code would look something like

ods excel file=<path> options(any options that apply to whole document>;

%mymacro(schema=firstsheet)

/* to change the sheet you may need to specify an option to force a new sheet*/
ods excel options(sheet_interval='table');
%mymacro(shema=othersheet);

ods excel close;

[kyle234]

Thank you!

[A_Kh · Accepted solution]

Hi, 

Try to add options again with sheet_interval='NOW' option.

eg.

ods excel file='test.xls'
	options( sheet_interval='NONE' sheet_name="class" doc='help');
	proc report data=sashelp.class;
		where sex='F';
	run;
	proc report data=sashelp.class;
		where sex='M';
	run;

	ods excel options( sheet_interval='now' sheet_name="cars");
	proc report data=sashelp.cars;
		where type='SUV';
	run;
	ods excel options( sheet_interval='none');
	proc report data=sashelp.cars;
		where type='Wagon';
	run;
ods excel close;

[Patrick]

The ODS EXCEL destination doesn't allow for updates to an existing Excel once it got closed. If it's just about adding another data sheet (a table) the libname xlsx engine should work but I wouldn't know how you could add another report. 

If you can call your macro in the same session then just only open the ODS EXCEL destination once, do all your data processing and reports (can be multiple macro calls) and close the destination at the very end. 

If above isn't an option because you want for example to update the Excel only later via a different SAS session then the only approach I can think of is to create separate Excel files and then call some 3rd party product like Python (openxl) or alternatively under Windows some Powershell or VB script out of SAS that copies/moves Excel sheets from one Workbook to another.

[Ksharp]

There are two way to do that ,but not by "ODS EXCEL".

But both would not keep the style of data,just put raw data into EXCEL file.

1) using LIBNAME+XLSX/EXCEL engine

2)using PROC EXPORT:

proc export outfile='c:\temp\test.xlsx' data=sashelp.class dbms=xlsx replace;
sheet='class';
run;
proc export outfile='c:\temp\test.xlsx' data=sashelp.heart dbms=xlsx replace;
sheet='heart';
run;