topic How do I add another worksheet to an existing workbook? in SAS Programming

How do I add another worksheet to an existing workbook?

kyle234 — Wed, 08 May 2024 21:52:45 GMT

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;

Re: How do I add another worksheet to an existing workbook?

ballardw — Wed, 08 May 2024 22:11:04 GMT

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;

Re: How do I add another worksheet to an existing workbook?

A_Kh — Wed, 08 May 2024 22:13:12 GMT

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;

Re: How do I add another worksheet to an existing workbook?

Patrick — Wed, 08 May 2024 22:21:07 GMT

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.

Re: How do I add another worksheet to an existing workbook?

Ksharp — Thu, 09 May 2024 01:18:19 GMT

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;

Re: How do I add another worksheet to an existing workbook?

kyle234 — Mon, 17 Jun 2024 13:26:24 GMT

Thank you!