How to unwrapped header text in proc report?

[ernie86]

This is the code for my proc report. Somehow the header is cut off and wrapped text. I wanted unwrapped the header but not sure what am I missing in my code.

ODS Tagsets.ExcelXP OPTIONS ( orientation='landscape' PAPERSIZE='LEGAL' sheet_name='SAMHSA_SA_OTP_Matched'
FITTOPAGE='yes' AUTOFIT_HEIGHT='yes' FROZEN_HEADERS='5' /*FROZEN_ROWHEADERS='6'*/) style=fancyprinter;

proc report data=SAMHSA_final nofs split=' *'
style(header)=[background = $color. foreground=BLACK BORDERCOLOR=black borderspacing=1 borderwidth=1
font_face=ARIAL font_size=1.5 font_weight=bold /*CELLWIDTH=100pt*/]
style(column)=[font_face=ARIAL font_size=1.5 BORDERCOLOR=BLACK borderspacing=1 borderwidth=1 /*CELLHEIGHT=11pt*/]
style(report)=[BORDERCOLOR=BLACK borderspacing=1 borderwidth=1 ];

column ('Substance Abuse and Mental Health Services Administration (SAMHSA) Provider Listing'
SAMHSA_NAME_1 SAMHSA_NAME_2 SAMHSA_STREET_1 SAMHSA_STREET_2
SAMHSA_CITY SAMHSA_STATE SAMHSA_ZIP SAMHSA_COUNTY SAMHSA_PHONE
SAMHSA_LATITUDE SAMHSA_LONGITUDE SAMHSA_Type_Facility);

define SAMHSA_NAME_1/display width=20 style={just=left foreground=black} style(header)={background=CXB0C4DE }
style(column)={background=CXB0C4DE} flow ;
define SAMHSA_NAME_2/display width=15 style={just=left foreground=black} style(header)={background=CXB0C4DE}
style(column)={background=CXB0C4DE} flow;
define SAMHSA_STREET_1/display width=15 style={just=left foreground=black} style(header)={background=CXB0C4DE}
style(column)={background=CXB0C4DE} flow;
define SAMHSA_STREET_2/display width=15 style={just=left foreground=black} style(header)={background=CXB0C4DE}
style(column)={background=CXB0C4DE} flow;
define SAMHSA_CITY/display width=10 style={just=left foreground=black} style(header)={background=CXB0C4DE}
style(column)={background=CXB0C4DE} flow;
define SAMHSA_STATE/display width=10 style={just=left foreground=black} style(header)={background=CXB0C4DE}
style(column)={background=CXB0C4DE} flow;
define SAMHSA_ZIP/display width=10 style={just=left foreground=black} style(header)={background=CXB0C4DE}
style(column)={background=CXB0C4DE} flow;
define SAMHSA_COUNTY/display width=10 style={just=left foreground=black} style(header)={background=CXB0C4DE}
style(column)={background=CXB0C4DE} flow;
define SAMHSA_PHONE/display width=10 style={just=left foreground=black} style(header)={background=CXB0C4DE}
style(column)={background=CXB0C4DE} flow;
define SAMHSA_LATITUDE/display width=10 style={just=left foreground=black} style(header)={background=CXB0C4DE}
style(column)={background=CXB0C4DE} flow;
define SAMHSA_LONGITUDE/display width=10 style={just=left foreground=black} style(header)={background=CXB0C4DE}
style(column)={background=CXB0C4DE} flow;
define SAMHSA_Type_Facility/display width=10 style={just=left foreground=black} style(header)={background=CXB0C4DE}
style(column)={background=CXB0C4DE} flow;

run;

This is the output now: 

This is what i want:

[Cynthia_sas]

Hi, Since you did not post data, no one can run your code. However I do not observe what you describe. Using SAS 9.4M5 and either ODS EXCEL or ODS TAGSETS.EXCELXP, I do not have any wrapping for the spanning header, as shown below:

  Notice that I took EVERYTHING out of the code except the font specification and my long header did not wrap.

    I have a few comments about your code, you've mixed some ODS options and style overrides with LISTING only options. For example WIDTH=15 or WIDTH=20 and FLOW are all listing only options that are ignored by ODS destinations.

  I also notice that you seem to NOT use units of measure  such as font_size=1.5? 1.5 what? pixels, points??? makes more sense to specify 10pt because 1.5 pt is really, really small. Even 1.5 pixels would be pretty tiny. You've commented out CELLHEIGHT. As you can see in my output, I didn't need to use CELLHEIGHT -- my fonts seem to be 10pt for the data cells and 12pt for the header cells. Did you want to go bigger or smaller than that?

  I didn't mess with borderwidths -- a borderwidth of 1, again is 1 pixel...I generally find you need a larger borderwidth for them to get bigger. Also there is a distinct chance that Excel won't honor your borderwidths.

  The use of 3 style overrides in 1 DEFINE statement is going to force ODS and PROC REPORT to try to resolve all the settings.

if you want some style elements in common across all items on the report, then put what you want in the overrides in the PROC REPORT statement -- but 3 overrides in one DEFINE is just confusing.

  I'm not seeing issues with my simplified version of your code. So my guess is something in your combination of overrides is messing up the spacing.

Cynthia