I need to generate a report with 2 tabs, and each tab should have a text note. however, the following code gave me a different result - there are 2 questions: 1. I used the following code but both the text notes are show up in the 1st tab(see the 1st and 2nd screenshot). 2. the subtotal(i.e. filtered total by sex) does not work (please see the 3rd screenshot) proc sql noprint;select sum(age),sum(height),sum(weight), sum(height)/sum(weight) format=percent8.2 into :s1,:s2,:s3,:ratio from sashelp.class; quit; %put s1=&s1. s2=&s2. s3=&s3. ratio=&ratio.; *tab1;; ods excel close; ods excel file="/sasdata/mi/rpma/rd/m16563t/Auto_Report/data/class1.xlsx" options(sheet_name="tab1" autofilter='1-2' SHEET_INTERVAL='PAGE'); ods escapechar='^'; ods text=" total age is | total hight is | total weight is | %% ratio "; ods text= " ^_^_ &s1. | &s2. | &s3. | &ratio.%% "; * ^_ %; proc report data=sashelp.class nocenter style(header)={just=l}; column name sex age height weight ratio; define name -- weight/ display; define age / analysis sum format=comma12. style(column)= [cellwidth=.5in]; define height / analysis sum format=comma12. style(column)= [cellwidth=.8in];*newly added column; define weight / analysis sum format=comma12. style(column)= [cellwidth=.8in];*newly added column; define Ratio / computed format=percent8.2; compute ratio; ratio = height.sum / weight.sum; endcomp; rbreak before / summarize style=[background=lightblue font_weight=bold];* CONTENTS='Total'; compute before; call define(age, 'style',"style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); call define(height,'style',"style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); call define(weight,'style',"style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); call define(Ratio, 'style',"style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); endcomp; run; *tab2;; ods excel options(sheet_name="tab2" autofilter='1-2' sheet_interval='now' SHEET_INTERVAL='PAGE'); ods escapechar='^'; ods text=" total age is | total hight is | total weight is | %% ratio "; ods text= " ^_^_ &s1. | &s2. | &s3. | &ratio.%% "; * ^_ %; proc report data=sashelp.class nocenter style(header)={just=l}; column name sex age height weight ratio; define name -- weight/ display; define age / analysis sum format=comma12. style(column)= [cellwidth=.5in]; define height / analysis sum format=comma12. style(column)= [cellwidth=.8in]; define weight / analysis sum format=comma12. style(column)= [cellwidth=.8in]; define Ratio / computed format=percent8.2; compute ratio; ratio = height.sum / weight.sum; endcomp; rbreak before / summarize style=[background=lightblue font_weight=bold]; compute before; call define(age,'style', "style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); call define(height,'style', "style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); call define(weight,'style', "style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); call define(Ratio,'style', "style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); endcomp; run; title; ods excel close; and results are: ... View more

proc sql noprint;select sum(age),sum(height),sum(weight), sum(height)/sum(weight) format=percent8.2 into :s1,:s2,:s3,:ratio from sashelp.class; quit; %put s1=&s1. s2=&s2. s3=&s3. ratio=&ratio.; ods excel close; ods excel file="c:///class1.xlsx" options(sheet_name="tab1" autofilter='1-2'); ods escapechar='^'; ods text=" total age is | total hight is | total weight is | % ratio "; ods text= " &s1. | &s2. | &s3. | &ratio. "; proc report data=sashelp.class nocenter; column name sex age height weight; define name -- weight/ display; define age / analysis sum format=comma12. style(column)= [cellwidth=.5in]; define height / analysis sum format=comma12. style(column)= [cellwidth=.8in];*newly added column; define weight / analysis sum format=comma12. style(column)= [cellwidth=.8in];*newly added column; rbreak before / summarize style=[background=lightblue font_weight=bold];* CONTENTS='Total'; compute before; call define(_row_,'style', "style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); *for all numeric columns sum; endcomp; run; title; ods excel close; I want to generate an.excel file with text in the report using ods excel/proc report line statement, but there are several questions:  1. can each header align to the left side of each cell? 2. the '%' is omitted in the .xlsx report (i.e. % ratio in ods text statement, they should show up in row1 and row 2 in the report), how I can keep it in the report? 3. the total (in line 4 in the report) for each column are 0s, and how can they display the correct numbers(i.e. the total age should 253 instead of 0). 4. is there any way to control the ods test length or format, because if the test is longer, the test will be wrapped in multiple rows(i.e. row 1 and row 2, I need it stay in one row) thanks! ... View more