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 " );
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"
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.
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.
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.