Lines Wrapping in ODS Excel

[garfield]

 I'm having issues when using ODS Excel with text wrapping.

For some reason line breaks are being placed in some of my text fields and text wrap is being turned on causing much of the text to get cut off even when there is plenty of width for the text to fit.

I saw this post, here, however none of the work arounds listed there worked for me.  Has anyone else figured out how to deal with this?

Below is an example, even with a super wide column width the text for player name still wraps.

ods Excel file="P:\test.xlsx"
OPTIONS( 	absolute_column_width='100')
;

PROC REPORT DATA = sashelp.baseball;
	COLUMNS
Name       
Team       
nAtBat     
nHits      
nHome      
nRuns      
nRBI       
nBB        
YrMajor    
CrAtBat    
CrHits     
CrHome     
CrRuns     
CrRbi      
CrBB       
League     
Division   
Position   
nOuts      
nAssts     
nError     
Salary     
Div        
logSalary       
;
RUN;
ODS EXCEL CLOSE;

[DavidPope]

Not the best work around especially from an efficiency standpoint because it requires you to pre-process your data, but a workaround non the less.

data temp1;

    set sashelp.baseball;

    Name=compress(tranwrd(Name,'20'x,'_'),'_',"C");

run;

ods Excel file="D:\test.xlsx"

OPTIONS( absolute_column_width='100')

;

PROC REPORT DATA = temp1;

COLUMNS

Name

Team

nAtBat

nHits

nHome

nRuns

nRBI

nBB

YrMajor

CrAtBat

CrHits

CrHome

CrRuns

CrRbi

CrBB

League

Division

Position

nOuts

nAssts

nError

Salary

Div

logSalary

;

RUN;

ODS EXCEL CLOSE;

[garfield]

Thanks for the suggestion!  However this won't work for what I actually need it for as I have a lot more text fields than this example, most of which require their spaces.

Any other ideas?  I can't believe this isn't a bigger issue for people.

[DavidPope]

Having read the other post you mentioned I found this worked.

You don't have to pre-process it, but you have to write a define statement for each column impacted.

ods Excel file="D:\test.xlsx"

OPTIONS(absolute_column_width='100')

;

PROC REPORT DATA = sashelp.baseball;

COLUMNS

Name

Team

nAtBat

nHits

nHome

nRuns

nRBI

nBB

YrMajor

CrAtBat

CrHits

CrHome

CrRuns

CrRbi

CrBB

League

Division

Position

nOuts

nAssts

nError

Salary

Div

logSalary

;

define Name/style(column)=[whitespace=pre] style(column)=data[width=100%];

RUN;

ODS EXCEL CLOSE;

[SuzanneDorinski · Accepted solution]

I've had this issue at work.  I run the code the first time, so that I can get decent column widths for all the other columns.  You can use a formula in Excel to have Excel show the column width.  

=CELL("width",A326) shows the width of the first column, then just copy the formula across the columns.  Then you can copy the resulting widths into your SAS program and adjust the widths for the columns with the text that you don't want to wrap.  

%let path=/folders/myfolders/ODS Excel examples;

ods Excel file="&path/long_text_not_wrapping.xlsx"
OPTIONS(absolute_column_width='20,20,6,5,6,5,5,6,9,7,7,7,7,7,7,10,9,12,5,8,6,12,9,11');
PROC REPORT DATA = sashelp.baseball split='*';
COLUMNS
Name
Team
nAtBat
nHits
nHome
nRuns
nRBI
nBB
YrMajor
CrAtBat
CrHits
CrHome
CrRuns
CrRbi
CrBB
League
Division
Position
nOuts
nAssts
nError
Salary
Div
logSalary
;
define name / "Player's*Name" style(column)={tagattr='wraptext:no' width=100%};
define team / "Team at the*End of*1986" style(column)={tagattr='wraptext:no' width=100%};
RUN;
ODS EXCEL CLOSE;

[garfield]

Thanks for this!  I was trying all of the other width options I could find and not having any luck.

FYI I had to combine this with the solution in the other thread I referenced for my data because some columns were still wrapping even though there was enough space.

Adding style(column)=data[width=1000% tagattr='wrap:no'] after the column definition in proc report fixed it.

Thanks again!

[chimanbj]

This is brilliantly simple! This can solve so many problems. Thanks so much!

[JanP]

Thank you for this simple approach to fixing this!  I have spent two days trying to find a way to stop my text from wrapping!  This solved the problem!