BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MarcoGuimaraes
Obsidian | Level 7
Thanks, Jadu! The attribute "width=100%" worked fine with ABSOLUTE_COLUMN_WIDTH .
Milo1
Calcite | Level 5

I changed my stylesheet from BarrettsBlue to Sasweb and the wrapping went away. Might be worth a try to check other styles.

AR_BB
Calcite | Level 5

This solution did not work for me

bartman
Fluorite | Level 6

I was looking for the same information and wonder if this fixes it:

"Text wrapping is handled differently in the Excel destination. The TAGATTR="WRAP:"YES | NO" attribute is included in the Excel destination to handle the same functionality as the WRAPTEXT="YES | NO" tagset option."

from A Ringside Seat: The ODS Excel Destination versus the ODS ExcelXP Tagset pg 14.

 

That didn't seem to change things.

 

But looking on pg 17 it shows using the TAGATTR setting on individual columns of a proc print var. I'm going to try that and get back here.

Chevell_sas
SAS Employee

The FLOW= sub-option was added with SAS 9.4M4 which prevents the hard return from being added to column forcing the wrap. Therefore, you can then specify the desired width using for instance the ABSOLUTE_COLUMN_WIDTH= and this will wrap based on the width specified for the column. Specifying the FLOW='Tables'  for example will prevent the hard return from being added to any of the columns within the table.  See page 2 in the below document for more information on the option.

 

http://support.sas.com/resources/papers/proceedings17/SAS0710-2017.pdf

Tom
Super User Tom
Super User

@Chevell_sas

That paper mentions inserting CRLF. Does it really insert both a CR and a LF character?  Or does it just insert just the CR character which is more common in multi line cells in Excel?  If so it could have a big impact on the usability of any CSV files generated from the Excel file.


@Chevell_sas wrote:

The FLOW= sub-option was added with SAS 9.4M4 which prevents the hard return from being added to column forcing the wrap. Therefore, you can then specify the desired width using for instance the ABSOLUTE_COLUMN_WIDTH= and this will wrap based on the width specified for the column. Specifying the FLOW='Tables'  for example will prevent the hard return from being added to any of the columns within the table.  See page 2 in the below document for more information on the option.

 

http://support.sas.com/resources/papers/proceedings17/SAS0710-2017.pdf


 

Chevell_sas
SAS Employee
@Tom

It does actually insert both CR and LF when this wrap occurs.
Tom
Super User Tom
Super User

So can we make a change request to have NOT do that in the future?  

 

There are literally hundreds of posts on the various SAS discussion boards about how to fix delimited files that have been corrupted in exactly that way by having end-of-line characters embedded in character variables.  The easiest solution is when the embedded line break is just a CR and the file is either delimited with CRLF (like on Windows/DOS) or LF (like on Unix).

 

data want ;
  infile 'mybadfile.csv' dsd firstobs=2 termstr=crlf ;
  length var1 - var20 $200;
  input var1-var20;
run;

When the literal end-of-line characters are embedded into a field it can be impossible or really hard to fix the file so that it can be read.

Chevell_sas
SAS Employee

The behavior was addressed with the M4 by adding the new FLOW= option which allows you to modify this behavior for individual parts of the table , the entire table,  the text or a specified range. So there is a way to prevent this default,  although the default has not changed.   

 

 

bartman
Fluorite | Level 6

Yes, on this Tips page, it states "You can also use the Excel destination's new FLOW= suboption, which is available in the fourth maintenance release for SAS 9.4 (TS1M4). This option contains the parameters TABLES, ROWHEADERS, DATA, TEXT, and a range (for example, A1:E20). When you specify the parameter TABLES, that automatically includes the HEADERS, ROWHEADERS, and DATA parameters."

 

http://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

Search for the word flow on the page - nice addition.

zazu
Calcite | Level 5

I've tried what Chevvel suggested and it didn't work BUT it turns out that if your table is bigger than a certain size as per SAS default you need to specify:

 

 

options papersize=(22in 27in);

  <rest of your ods code >

or whatever size it is that  you need!

 

hope it works for you

 

 

Jay_TxOAG
Quartz | Level 8

I have tested this on 9.4M4 and 9.4M6

We use Proc Report in our Stored Process reports with links to Excel. Using AUTOMATIC_COLUMN_WIDTH in the ODS EXCEL options and CELLWIDTH as a STYLE option on the DEFINE statement in PROC REPORT, we get some control over our EXCEL cells.

 

 

ODS EXCEL File="C:\COLUMN WIDTH TEST.xlsx";

ODS EXCEL Options(sheet_name="DESC COLUMN BREAKS"
absolute_column_width= '50,50,50');
proc report data = sashelp.moniker nowd;
Column Client Server Desc;
Define Client / "Client";
Define Server / "Server";
Define Desc / "Desc" ;
run ;

ODS EXCEL Options(sheet_name="DESC COLUMN NO BREAKS"
absolute_column_width= '50,50,50');
proc report data = sashelp.moniker nowd;
Column Client Server Desc;
Define Client / "Client";
Define Server / "Server";
Define Desc / "Desc" style(column) = {cellwidth=3.75in};
run ;

 

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