ODS EXCEL multiple sheets for each iteration in a macro

[Rohit_R]

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

[HN2001]

Try the solution in this link

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

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