Thanks Cynthia, yes its ODS EXCEL. SPANROWS worked fine in displaying the region as single merged cell in Excel . I have the following requests. 1. How to rotate the values. 2. I need to insert a blank column in Column A (First column for better appeal). 3. How to outline the title 1 as in the attachment (Row 2). 4. How to get the sub-total in the column C (product column) without displaying the region name in the column B(region column). I tried using the below but got Region name displayed in the column B. compute after Region; Reporting_Product='Sub-total'; endcomp; I have pasted the code below . column names are made up for organizational security reasons. ODS TAGSETS.EXCELXP OPTIONS ( Sheet_Name = 'Exposures' Absolute_Column_Width = '15,20,12,12,8,8,8,8,8,8' sheet_interval='none' Orientation = 'portrait' HIDDEN_COLUMNS='none' Row_Heights = '20,20,15,15,15,15,15' zoom='85' Center_Horizontal = 'yes' ); proc report data=salesdata spanrows nowd style(header)={font_weight=bold background=lightgreen}; title1 ' Appendix 1' ; title2 JUSTIFY=left font=ARIAL height=10pt ' ' ; title3 JUSTIFY=left font=ARIAL height=10pt ' title2' ; title4 JUSTIFY=left font=ARIAL height=10pt ' title3'; title5 JUSTIFY=left font=ARIAL height=10pt ' title4'; title6 JUSTIFY=left font=ARIAL height=10pt ' title 5'; title7 JUSTIFY=left font=ARIAL height=10pt ' additional line'; column Region Reporting_Product Sales Returns Overhead1 Overhead2 Overhead3 Overhead4 Overhead5 ; define Region / group ' ' style(column)=Header{vjust=middle just=left background=lightgreen} /*style= vcentre style(header)={vjust=middle} style(column)=[vjust=middle just=left]*/; define Reporting_Product / group 'Product Asset Group' ; define Sales /display analysis sum style={tagattr='format:0.0,,' }; define Returns /display analysis sum style={tagattr='format:0.0,,' }; define Overhead1 /display analysis sum style={tagattr='format:0.0,,' }; define Overhead2 /display analysis sum style={tagattr='format:0.0,,' }; define Overhead3 /display analysis sum style={tagattr='format:0.0,,'}; define Overhead4 /display analysis sum style={tagattr='format:0.0,,'}; define Overhead5 /display analysis sum style(Column)={tagattr='format:0.0,,'}; break after Region/ style={tagattr='format:0.0,,' font_weight=bold} summarize; rbreak after / summarize style={tagattr='format:0.0,,' font_weight=bold}; compute after Region; Region='Sub-total'; endcomp; compute after; Region='Total'; endcomp; run;
... View more