Search code examples
excelsasheaderreportproc

SAS Proc report - ODS EXCEL column/header width

I did a proc report and the HTML result shown in SAS gives me what i want which is :

enter image description here

But in EXCEL the output i get doesn't display the text horizontally it skips a line :

enter image description here

Here is my code :

ods excel file = "" 
    options(sheet_interval='none' embedded_titles='yes') ; 

ods escapechar = '^'  ;
options missing = 0 orientation=landscape center ; 

proc report data = a split = '-' 
                                style(header)={background=white borderwidth=1 bordercolor=black width=150 color=black just=c textalign=c} 
                                style(report)={borderwidth=1 bordercolor=black just=c}
                                style(column)= {borderwidth=1 bordercolor=black just=r color=black tagattr='format:###,###,###,###,###0'}
                                style(summary)= [just=c textalign=c];
;

column ( '^{style[color=red]Total}'  ('Category (before change/extension) - Performing time'
        var1 var2 var3 );

define var1 / group; 
define var2 / analysis sum ; 
define var3 / analysis sum ; 

run ;
Solution

  • Have you seen the ods excel option with flow for tables? I had the same issue and it helped with mine; from the SAS support document:

    The ODS Excel destination is a measured destination that uses an algorithm to determine when text should wrap within a cell. This wrapping algorithm creates a best fit for columns so that they are not overly wide. When text does wrap within a cell, a carriage-return/line-feed character (CRLF) is added where the line break occurs.

    ...the most dynamic method is to use the FLOW= tagset option. The fourth maintenance release for SAS® 9.4 (TS1M4) introduces the ODS Excel destination's FLOW= suboption. When this option is specified, the Excel destination does not insert newline characters to force the text to wrap in the part of the output that is specified as an argument in the option. The FLOW= option also turns on the Wrap Text feature in Excel so that Excel will wrap the text to the column width.

    Value Area Effected TABLES Enables flow of column headings, row headings, and data cells. HEADERS Enables flow for headings only. ROWHEADERS Enables flow for row headings. DATA Enables flow for data cells only.

    The following example demonstrates the use of the ODS Excel destination along with the TITLE_FOONTNOTE_BREAK=, ABSOLUTE_COLUMN_WIDTH=, and FLOW= options.

    Example 1. 
ods excel file="c:\temp1.xlsx" options(embedded_titles="Yes" 
                                        title_footnote_nobreak="yes" 
                                        flow="header,data" 
                                        absolute_column_width="75px,50px, 
                                        70px,50px,100px,125px,300px"); 
                                        ods text="Confidential Report";