Title in Proc Report does not span correctly on next sheet when using different number of columns

[Ani_Abby]

Hi,

I have question on TITLE when using twice into 2 sheets in the same Excel report. My sample coding as below

ODS _ALL_ CLOSE;

DATA REPORT_DATA;
SET WORK.TOP_SUMMARY;

WHERE ROW_NO <= 20;
RUN;

PROC SORT DATA=WORK.REPORT_DATA;
BY ROW_NO;
RUN;

ods tagsets.excelxp file="/u01/Report/TOP20.xls";

options linesize=max;

title;
footnote;

ods tagsets.ExcelXP 
options(sheet_interval='none' 
sheet_name='FINAL20' 
embedded_titles="yes"             
EMBEDDED_FOOTNOTES='On' 
TITLE_FOOTNOTE_NOBREAK='YES'
ABSOLUTE_COLUMN_WIDTH = '3, 50, 12'

);



proc report data=REPORT_DATA nowd 
style(report)=[width=100% cellspacing=2 cellpadding=10] split='*' center
style(header)=[backgroundcolor=very light grey
color=black FONTWEIGHT=BOLD cellpadding=30 cellspacing=20]
style(column)=[backgroundcolor=white color=black cellspacing=5 cellpadding=20]
;


column ROW_NO
CUST_NAME
TOTAL
;


DEFINE ROW_NO / "No." DISPLAY ORDER=DATA MISSING style={tagattr="format:#,##0" cellwidth=0.3in}    ;
DEFINE CUST_NAME/ "CUSTOMER" ORDER=DATA MISSING style= {tagattr="type: String" cellwidth=4.0in }    ;
DEFINE TOTAL/ "TOTAL*AMOUNT" ANALYSIS SUM ORDER=DATA MISSING style={tagattr="format:#,##0" cellwidth=1.2in} ; 

RBREAK AFTER / STYLE=[FONTWEIGHT=BOLD] SUMMARIZE;

title1 justify=center height=10pt bold color=black  bspace=2 BCOLOR=WHITE 'Main Page 1';
title2 justify=center height=10pt blank=yes bold color=black bspace=2 BCOLOR=WHITE "TOP 20";

footnote justify=left height=10pt bcolor=white color=black 'Total include Product 1, 2 and 3';



RUN;


QUIT;

proc sql;
drop table work.report_data;
QUIT;


ods tagsets.ExcelXP options(sheet_interval='none' sheet_name='DETAIL 20'
EMBEDDED_FOOTNOTES='Off'
TITLE_FOOTNOTE_NOBREAK='YES'
) ;
options linesize=max;

footnote;
title;

DATA REPORT_DATA_DETAIL;
SET WORK.TOP_REPORT_DETAIL;

WHERE ROW_NO <= 20 ;
run;


PROC SORT DATA=WORK.REPORT_DATA_DETAIL;
BY NO;
RUN;


proc report data=REPORT_DATA_DETAIL nowd center
style(REPORT)= [width=90%   cellspacing=2 cellpadding=5]
style(header)=headerstrong{backgroundcolor=very light grey
                                      color=black FONTWEIGHT=BOLD}
style(column)=[cellspacing=2 cellpadding=10]

;



column
ROW_NO
CUST_NAME
PRODUCT_1
PRODUCT_2
PRODUCT_3
TOTAL
;
DEFINE ROW_NO / "No." DISPLAY ORDER=DATA MISSING style={tagattr="format:#,##0" cellwidth=0.3in}    ;
DEFINE CUST_NAME / "CUSTOMER" ORDER=DATA MISSING style= {tagattr="type: String" cellwidth=4.0in }    ;
DEFINE PRODUCT_1 / "PRODUCT 1" ANALYSIS SUM ORDER=DATA MISSING style={tagattr="format:#,##0" cellwidth=1.1in} ; 
DEFINE PRODUCT_2 / "PRODUCT 2" ANALYSIS SUM ORDER=DATA MISSING style={tagattr="format:#,##0" cellwidth=1.1in} ; 
DEFINE PRODUCT_3 / "PRODUCT 3" ANALYSIS SUM ORDER=DATA MISSING style={tagattr="format:#,##0" cellwidth=1.1in} ; 
DEFINE TOTAL / "TOTAL" ANALYSIS SUM ORDER=DATA MISSING style={tagattr="format:#,##0" cellwidth=1.1in} ; 

RBREAK AFTER / STYLE=[FONTWEIGHT=BOLD] SUMMARIZE;

title1 justify=center height=10pt bold color=black  bspace=2 BCOLOR=WHITE "main pg 2";
title2 justify=center height=10pt blank=yes bold color=black bspace=2 BCOLOR=WHITE "TOP 20 DETAIL";
footnote;

RUN;

QUIT;

proc sql;
drop table work.REPORT_DATA_DETAIL;
QUIT;


QUIT;



/* end of reporting */
ods tagsets.ExcelXP close;

My problem, in the second sheet, the TITLE only span up to third column instead spanning to all 6 columns as per data displayed.

Row_No	Cust_Name	Total
1	ABC	10000
2	DEF	9900

Sample Table 1

Row_No	Cust_Name	Product_1	Product_2	Product_3	Total
1	ABC	5000	0	5000	10000
2	DEF	300	5500	4100	9900

Sample table 2

[Cynthia_sas · Accepted solution]

Hi:
if you put options(doc='Help') on your ods tagsets.excelxp statement, you'll see that there is a suboption called Title_Footnote_Width that allows you to control the number of columns the embedded title will span.

Cynthia

[Ani_Abby]

Thank you. It works for my SAS 9.4 . 🙂