BookmarkSubscribeRSS Feed
Rohit_R
Obsidian | Level 7

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

1 REPLY 1
HN2001
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 4221 views
  • 0 likes
  • 2 in conversation