I just started using the excelxp tagset and I've succeeded in having it create multiple worksheets within a workbook, with customized headers, and lots of other nifty features. Since I am trying to duplicate an existing Excel output format, now I need to force hard page breaks, within worksheets, when the value of a variable changes. The goal is to be able to print the worksheet and have each value of this variable start on a new page. I'm using proc report, and have tried using a compute block to at least add a line at the break point (I figure if I can output a line, I can then replace it with the actual XML code for a hard page break, once I find out what that is!) but I get an error message that there is a problem with the table when I try to open the file in Excel. Here's my code:
ods path work.cas(update) sasuser.templat(update) sashelp.tmplmst(read);
proc template;
define style styles.CASXL; * just creating a copy;
parent=styles.sansprinter;
end;run;quit;
%let title1 = "&campaigndesc - from &strtdt to &enddt";
%let title2 = "Data as of &asofdt";
ods listing close;
ods tagsets.excelxp file="&campaignpath.\&sharepointfolder.\&distnm._&gekkofileprefix.&mth..xml"
options(embedded_titles='yes'
frozen_headers='4'
orientation = 'Landscape'
suppress_bylines='Yes'
sheet_interval='bygroup'
autofit_height='Yes'
pages_fitwidth='1'
row_heights='0,0,0,30,0,0,0'
sheet_label=' '
pagebreaks='no'
) style=CASXL;
title1 j=center font='Ariel bold' height=18pt &title1;
title2 j=center height=12pt &title2;
proc report data=final nowindows
style(header) = {background=cx99ccff foreground=black font_size = 8pt FONT_WEIGHT = bold}
style(column) = {font_size = 8pt};
by pntname;
column pntname offname fcoff fcno FCNAME Client_Name acctnum2
numsecs totcddolX earliestmatdte hhasx &specialist1 pthcorid;
define pntname / display order 'Complex' style(column) = {cellwidth=100pt } ;
define offname / display order 'Office Name' ;
define fcoff / display order 'Office Number' style(column) = {cellwidth=50pt } ;
define fcno / display order 'FA Number' style(column) = {tagattr='format:@' cellwidth=50pt } ;
define fcname / display order 'FA Name';
define Client_name / display order 'Client Name';
define acctnum2 / display 'Account Number' style(column) = {tagattr='format:@' cellwidth=50pt } ;
define numsecs / format=comma6.0 'Number of Maturing CDs' style(column) = {cellwidth=50pt } ;
define totcddolX / style(column) = {tagattr='format:$##,###,###,##0' cellwidth=50pt }
'Total Market Value ($)' ;
define earliestmatdte / 'Earliest Maturity Date' style(column) = {cellwidth=50pt } ;
define hhasx / display style(column) = {tagattr='format:$##,###,###,##0' cellwidth=50pt }
'Total HH Assets ($)' ;
define &specialist1 / display 'Specialist' style(column) = {cellwidth=100pt } ;
define pthcorid / display 'PATH Core ID' style(column) = {tagattr='format:@' cellwidth=100pt } ;
compute after fcname ;
line ' ';
endcomp;
run;
ods tagsets.excelxp close;
ods listing;
run;
... View more