Multi Sheet Excel Workbook Macro Using tagsets.excelx - Only 1 Sheet Generating

[Mgarret]

Hi all,

I’m trying to write a macro which produces a multi sheet excel workbook using tagsets.excelx.  When I run this macro, even though I have 4 passes written at the bottom, which should create a workbook containing 4 sheets produced, only the last sheet (RLEWOLUXA) is generated and contained in the workbook not all 4 sheets. Not sure what code I am missing / not doing.  

Any assistance will be greatly appreciated.   

%macro datasets ( LD=, SD=);
ods listing close;


options
rightmargin=.1in
leftmargin=.1in
Bottommargin=.1in
topmargin=.1in
Papersize='legal';

ods tagsets.excelxp file="/sasdata/mine/datasets/Report.xls" 
style=minimal
options(
ORIENTATION='portrait'
AUTOFIT_HEIGHT='yes'  
embedded_titles='yes'
PAGE_ORDER_ACROSS='yes'
sheet_name=&SD.);
 
TITLE JUSTIFY=l FONT='Arial Narrow'  HEIGHT=14pt &LD.;

proc report
data=check nowd 
style(column)={just=c font_size=11pt font_face= 'Arial Narrow'cellwidth=2.in}
style(header)={font_size=13pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1};
column 
c_Total_Responders
m_Total_Responders
Total_Responders_diff
c_total_customers
m_total_customers
total_customers_diff
c_Total_Target_Sales
m_Total_Target_Sales
Total_Target_Sales_diff
c_Total_All_Sales
m_Total_All_Sales
Total_All_Sales_diff;

where Customer_Status =1 and short_description=&sd.;
run; quit; 
title; 
ods tagsets.excelxp close;
ods listing;
%mend ;


%datasets (LD="Men's Apparel-Core Model      ", SD="RLEMECORA ");  
%datasets (LD="Women’s Apparel-Core Model    ", SD="RLEWOCORA ");  
%datasets (LD="Men's Apparel-Luxury Model    ", SD="RLEMELUXA ");
%datasets (LD="Women's Apparel-Luxury Model  ", SD="RLEWOLUXA " );

[Reeza]

This line is your issue. Its not dynamic so basically it overwrites your file each time. You need to make the file name dynamic with a macro variable or parameter so that each iteration generates a unique file. 

ods tagsets.excelxp file="/sasdata/mine/datasets/Report.xls" 

[Mgarret]

Thanks.  Question: but if I have a unique file for each, how to a combine them all into one workbook via the macro.   I will ultimately have many more than 4 passes and will take forever to combie into one workbook. 

[Reeza]

Sorry, if you want multiple sheets - move the ods Tagsets open/close statements OUTSIDE of your macro call. 

options
rightmargin=.1in
leftmargin=.1in
Bottommargin=.1in
topmargin=.1in
Papersize='legal';

ods tagsets.excelxp file="/sasdata/mine/datasets/Report.xls" 
style=minimal
options(
ORIENTATION='portrait'
AUTOFIT_HEIGHT='yes'  
embedded_titles='yes'
PAGE_ORDER_ACROSS='yes'
sheet_name=&SD.);

%datasets (...);
%datasets (...);
%datasets (...);
%datasets (...);

ods tagsets.excelxp close;

Or because you're only using one PROC REPORT have you considered not using a macro at all and using BY group processing to generate the different tabs - look at Sheet_interval option in tagsets.

[Mgarret]

Unfortunately  that did not seem to work. When I try and open the file, I get an error.  

ods listing close;
%macro datasets ( LD=, SD=);
 
options
rightmargin=.1in
leftmargin=.1in
Bottommargin=.1in
topmargin=.1in
Papersize='legal';
ods tagsets.excelxp file="/sasdata/mine/datasets/Report.xls" 
style=minimal
options(
ORIENTATION='portrait'
AUTOFIT_HEIGHT='yes' 
embedded_titles='yes'
PAGE_ORDER_ACROSS='yes'
sheet_name=&SD.);

TITLE JUSTIFY=l FONT='Arial Narrow' HEIGHT=14pt &LD.;
proc report
data=check nowd 
style(column)={just=c font_size=11pt font_face= 'Arial Narrow'cellwidth=2.in}
style(header)={font_size=13pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1};
column 
c_Total_Responders
m_Total_Responders
Total_Responders_diff
c_total_customers
m_total_customers
total_customers_diff
c_Total_Target_Sales
m_Total_Target_Sales
Total_Target_Sales_diff
c_Total_All_Sales
m_Total_All_Sales
Total_All_Sales_diff;
where Customer_Status =1 and short_description=&sd.;
run; quit; 
title; 
ods listing;
%mend ;
%datasets (LD="RLE Men's Apparel-Core Model ", SD="RLEMECORA "); 
%datasets (LD="RLE Women’s Apparel-Core Model ", SD="RLEWOCORA "); 
%datasets(LD="RLE Men's Apparel-Luxury Model ", SD="RLEMELUXA"); 
%datasets (LD="RLE Women's Apparel-Luxury Model ", SD="RLEWOLUXA" );
ods tagsets.excelxp close;

[Reeza]

Your macro needs to be sandwiched between the calls.

But I still think using Sheet_Interval is a better option.

ods listing close;
%macro datasets_P3 ( LD=, SD=);
 
ods tagsets.excelxp
options(sheet_interval='proc' 
ORIENTATION='portrait'
AUTOFIT_HEIGHT='yes' 
embedded_titles='yes'
PAGE_ORDER_ACROSS='yes'
sheet_name=&SD.);

TITLE JUSTIFY=l FONT='Arial Narrow' HEIGHT=14pt &LD.;
proc report
data=check nowd /*headline headskip*/
style(column)={just=c font_size=11pt font_face= 'Arial Narrow'cellwidth=2.in}
style(header)={font_size=13pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1};
column 
c_Total_Responders
m_Total_Responders
Total_Responders_diff
c_total_customers
m_total_customers
total_customers_diff
c_Total_Target_Sales
m_Total_Target_Sales
Total_Target_Sales_diff
c_Total_All_Sales
m_Total_All_Sales
Total_All_Sales_diff;
where Customer_Status =1 and short_description=&sd.;
run; quit; 
title; 

%mend ;


options
rightmargin=.1in
leftmargin=.1in
Bottommargin=.1in
topmargin=.1in
Papersize='legal';
ods tagsets.excelxp file="/sasdata/mine/datasets/Report.xls" 
style=minimal;


%datasets_P3 (LD="RLE Men's Apparel-Core Model ", SD="RLEMECORA "); 
%datasets_P3 (LD="RLE Women’s Apparel-Core Model ", SD="RLEWOCORA "); 
%datasets_P3 (LD="RLE Men's Apparel-Luxury Model ", SD="RLEMELUXA"); 
%datasets_P3 (LD="RLE Women's Apparel-Luxury Model ", SD="RLEWOLUXA" );
ods tagsets.excelxp close;
ods listing;