ODS EXCEL multiple sheets for each iteration in a macro

Rohit_R — Wed, 20 Jun 2018 16:11:08 GMT

Hi,

I am running a macro loop to execute a proc tabulate multiple times for one variable and runs again multiple times for another variable.

I want to output each variable's multiple tabulate runs on separate sheets. I have tried using a different sheet name however its not working and I am getting all the proc tabulate outputs in one single sheet.

example code:

%MACRO Tabulate_rolls;
%local j i count;

%do j = 1 %to %sysfunc(countw(&DateList)); /*Datelist = List of months*/
%LET Month = %Scan(&DateList, &j);

ODS EXCEL OPTIONS(sheet_name = "&Month.");
%do i=1 %TO &RollforMonths;
%let CurrMonth=%scan(&ARBList, &j );
%let NextMonth=%scan(&ARBlist, %sysfunc(INTCK(month, %sysevalf('01JAN2011'd), %sysevalf("&NewBizMonth."d) ))+1+&i );

PROC Tabulate DATA=data1 MISSING;
CLASS &CurrMonth &NextMonth ;
TABLE &CurrMonth="", &NextMonth*(ROWPCTN=""*f=Percent_pict.);
RUN;

%end;

%end;
%mend;

ODS EXCEL FILE="C:\temp\New Microsoft Excel Worksheet.xlsx"
OPTIONS(sheet_interval = "none");
/*Run the New business flow*/
%Tabulate_rolls;
ODS EXCEL CLOSE;
%PUT _USER_;

I am expecting that for each iteration of the first Do loop (with j = 1 to %sysfunc....) the ODS EXCEL OPTIONS will create another sheet with the name from &Month and write all the iterations of the internal do loop (with i = 1 to rollformonths) proc tabulate to the new sheet.

However, it doesnt work and it writes all the output in one single sheet . I have tried playing with the sheet_interval option but it primarily creates one sheet for every run of proc tabulate.

Any help will be greatly appreciated!

Thanks,

Rohit

Re: ODS EXCEL multiple sheets for each iteration in a macro

HN2001 — Wed, 20 Jun 2018 16:23:56 GMT

Try the solution in this link

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-excel-amp-multiple-sheets/m-p/261953/highlight/true#M15551

there is a bug and you have to create a dummy table code to make this work and put each output on it's own tab

topic Re: ODS EXCEL multiple sheets for each iteration in a macro in ODS and Base Reporting

ODS EXCEL multiple sheets for each iteration in a macro

Re: ODS EXCEL multiple sheets for each iteration in a macro