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
Thank you. It works for my SAS 9.4 . 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.