The SAS Output Delivery System and reporting techniques

ODS EXCEL multiple sheets for each iteration in a macro

Occasional Contributor
Posts: 12

ODS EXCEL multiple sheets for each iteration in a macro



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.);  




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


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!




Occasional Contributor
Posts: 12

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

Try the solution in this link



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

Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation