BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
garfield
Fluorite | Level 6

 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;
1 ACCEPTED SOLUTION

Accepted Solutions
SuzanneDorinski
Lapis Lazuli | Level 10

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;

View solution in original post

7 REPLIES 7
DavidPope
SAS Employee

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
Fluorite | Level 6

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
SAS Employee

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
Lapis Lazuli | Level 10

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
Fluorite | Level 6

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
Obsidian | Level 7
This is brilliantly simple! This can solve so many problems. Thanks so much!
JanP
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 22990 views
  • 8 likes
  • 5 in conversation