Creating Multiple table in multiple sheets in Excel using SAS Macro

[Satyakshma]

Hi,

I have 9 segments in my data. For each segment I have 3 scenarios (Base, best and worst) and 3 separate datasets for these scenarios .

I want to generate an excel file with each segment(Seg1, seg2, Seg3---Seg9) as sheet name and 3 tables on each sheet, which means I need an excel file with 9 sheets on it, each sheet having 3 tables.

I need to create a macro for this as I need to make it dynamic.

Thank you for your help.

Satyakshma Rawat

[PaigeMiller]

---

@Satyakshma wrote:

Hi,

I have 9 segments in my data. For each segment I have 3 scenarios (Base, best and worst) and 3 separate datasets for these scenarios .

I want to generate an excel file with each segment(Seg1, seg2, Seg3---Seg9) as sheet name and 3 tables on each sheet, which means I need an excel file with 9 sheets on it, each sheet having 3 tables.

I need to create a macro for this as I need to make it dynamic.

---

Generally, you can't put three sheets on one table via either PROC EXPORT or ODS EXCEL. You could combine the 3 separate data sets into one, and then put the one big data set on a sheet.

However, aside from that, you can get what you want via ODS EXCEL without macros, using one big data set with a variable indicating segment and the SHEET_INTERVAL='BYGROUP' and SHEET_LABEL='Seg' options.

[andreas_lds]

The following code creates one sheet with three tables:

ods excel file="multi.xlsx" options(sheet_interval= 'none');

proc print data=sashelp.class(obs=3) noobs;
run;

proc print data=sashelp.cars(obs=3) noobs;
run;

proc print data=sashelp.baseball(obs=3) noobs;
run;

ods excel close;

[Reeza]

To make a macro you first need working code. It also depends on what you mean by a "table". If each table is a different breakdown it may not be worth a macro at all if it's just some proc prints.

Please provide an example of your base case - what the manual code looks like and what parts need to be generalized.