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

Ani_Abby — Mon, 01 Apr 2019 09:04:09 GMT

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

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

Cynthia_sas — Mon, 01 Apr 2019 12:54:47 GMT

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

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

Ani_Abby — Tue, 02 Apr 2019 08:04:51 GMT

Thank you. It works for my SAS 9.4 . 🙂

topic Re: Title in Proc Report does not span correctly on next sheet when using different number of column in ODS and Base Reporting

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

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

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