Hi, I am trying create an excel report where each tab contains several reports where a new tab is created depening on the value within a macro field. The report itself is a macro that is called several times per tab and the call contains the value that names the tab. Below is a simplified version of the code: ods tagsets.excelxp file= "&outpath\&jn - &title Rolling 13 Months &sysdate..xls" options(sheet_interval = "none" embedded_titles = "yes" embedded_footnotes = "yes" skip_space = '0,0,0,0,0'); %macro all_report(product,characteristic,title3,char_format); %macro dummy; %mend dummy; ods tagsets.excelxp options(sheet_interval = "none" sheet_name="&Product." ); Proc report data=SAS_DATA.rfv_storecards_all_months_&product. nowd; title1 justify=left " "; title2 justify=left " "; title3 justify=left "&title3."; Columns customergroup &characteristic._&dt_month3_year3. &characteristic._&dt_month2_year2. &characteristic._&dt_month1_year1. &characteristic._&dt_month0_year0.; Define customergroup / order order=internal "Customer Group"; Define &characteristic._&dt_month3_year3. / group "&dt_month3_year3." style(column)={tagattr=&char_format.}; Define &characteristic._&dt_month2_year2. / group "&dt_month2_year2." style(column)={tagattr=&char_format.}; Define &characteristic._&dt_month1_year1. / group "&dt_month1_year1." style(column)={tagattr=&char_format.}; Define &characteristic._&dt_month0_year0. / group "&dt_month0_year0." style(column)={tagattr=&char_format.}; run; %mend; %all_report(retailer1,customer,Customers,'format:#,##'); %all_report(retailer1,netsales122,Net Sales,'format:£#,##0,00'); %all_report(retailer1,trans122,Transactions,'format:#,##'); %all_report(retailer1,ATV,ATV,'format:£#,##0.00'); %all_report(retailer1,ASS,ASS,'format:£#,##0.00'); %all_report(retailer2,customer,Customers,'format:#,##'); %all_report(retailer2,netsales122,Net Sales,'format:£#,##0,00'); %all_report(retailer2,trans122,Transactions,'format:#,##'); %all_report(retailer2,ATV,ATV,'format:£#,##0.00'); %all_report(retailer2,ASS,ASS,'format:£#,##0.00'); ods _all_ close; What I want to end up with is a tab called 'retailer1' with 5 reports on it and a second tab called 'retailer2' with 5 reports on it. The variable 'product' in the call statement holds the name for the tab. I have tried several places to put the sheet-Interval and sheet_name options but no joy yet. Any ideas? many thanks
... View more
