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
