topic Re: Avoid line breaks when exporting long text to Excel in SAS Programming

Avoid line breaks when exporting long text to Excel

ChrisNZ — Wed, 28 Jul 2021 04:58:13 GMT

When running this:

ods excel file='%temp%\reports.xlsx' style=seaside options(flow="tables");
data T ;
  format X $256.;
  X='Sed ut perspiciatis, unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam eaque ipsa, quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt, explicabo.';
proc print;
run;
ods excel close;

I get this:

I tried using the flow option (which I don't need) to no avail.

Option absolute_column_width doesn't seem to be effective for this either.

Any way to avoid the line break?

Re: Avoid line breaks when exporting long text to Excel

andreas_lds — Wed, 28 Jul 2021 06:46:06 GMT

Using m5 the text seems to be misplaced, too:

The problem gets worse, when using Report Writing Interface and merged cells, so there seems to be a problem in ods excel.

ods excel file= '%temp%\rwi.xlsx' options (flow= 'tables' embedded_titles='yes');

data _null_;
   set sashelp.class end=jobDone;

   if _n_ = 1 then do;
      declare odsout rwi();
      rwi.table_start();
      rwi.head_start();
      rwi.row_start();
      rwi.format_cell(data: 'Long text, but not to long to fit above the header', column_span: 5);
      rwi.row_end();
      rwi.row_start();
      rwi.format_cell(data: 'Name');
      rwi.format_cell(data: 'Age');
      rwi.format_cell(data: 'Sex');
      rwi.format_cell(data: 'Weight');
      rwi.format_cell(data: 'Height');
      rwi.row_end();
      rwi.head_end();
      rwi.body_start();
   end;

   rwi.row_start();
   rwi.format_cell(data: Name);
   rwi.format_cell(data: Age);
   rwi.format_cell(data: Sex);
   rwi.format_cell(data: Weight);
   rwi.format_cell(data: Height);
   rwi.row_end();

   if jobDone then do;
      rwi.body_end();
      rwi.table_end();
   end;
run;

ods excel close;

Re: Avoid line breaks when exporting long text to Excel

Ksharp — Wed, 28 Jul 2021 12:47:54 GMT

Try this style:

ods excel file='c:\temp\report.xlsx' style=seaside ;
data T ;
  format X $256.;
  X='Sed ut perspiciatis, unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam eaque ipsa, quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt, explicabo.';
proc report data=t nowd;
columns x;
define x/style={ tagattr='wrap:no'};
run;
ods excel close;

Re: Avoid line breaks when exporting long text to Excel

johnsville — Wed, 28 Jul 2021 13:46:31 GMT

I have had this same problem for years. Adding the tag attribute wraptext=no did not solve the issue, although I now use that by default in every ODS/Excel report I generate. Buried in discussions here years ago the only solution that effectively fixed it involved processing each row beforehand, then explicitly setting the cell length to be the maximum length encountered when you kick off ODS. The short story is that ODS/Excel seems to compute a total row length on its own, using assumptions about a display window size, and then wraps text in cells for rows that are longer than the row length it somehow arrives at..

IF there is something easier that since then seems to solve the issue, I never caught wind of it. So I am waiting to be educated !

Re: Avoid line breaks when exporting long text to Excel

Tom — Wed, 28 Jul 2021 13:55:53 GMT

What version of SAS are you using? The FLOW="TABLES" option was added in 9.4M4.

https://communities.sas.com/t5/ODS-and-Base-Reporting/Lines-Wrapping-in-ODS-Excel/td-p/154925

Re: Avoid line breaks when exporting long text to Excel

johnsville — Wed, 28 Jul 2021 13:56:53 GMT

9.4 (TS1M4)

Re: Avoid line breaks when exporting long text to Excel

Tom — Wed, 28 Jul 2021 15:39:28 GMT

Your code works for me, SAS did not insert any HARD line breaks into the field.

Now if I make the column narrower then Excel will wrap the text.

Unless I tell Excel not to "Wrap Text" for that cell/column.

Are you asking how to remove that Excel Wrap Text option from the cell?

I think you might need to ask SAS support.

Re: Avoid line breaks when exporting long text to Excel

ChrisNZ — Thu, 29 Jul 2021 22:34:52 GMT

It's not about text wrap, that's why I included the formula bar in my screenshot.

It seems like nothing changes the behaviour.

So flow= is not in M2 supported apparently,

absolute_column_width = enlarges the cell but does not remove the line break.

tagattr='wrap:no' also has no effect; It probably isn't supported in M2 either.

I'll blame my old 9.4M2 version then. We should upgrade to M7 in the next quarter. Or the next. I might have moved on by then...

Thank you all for your suggestions. Unsure how to chose a solution as I couldn't obtain a result, most likely due to my out-of-date SAS version rather than a non-working answer.

Re: Avoid line breaks when exporting long text to Excel

andreas_lds — Thu, 29 Jul 2021 05:26:09 GMT

This could be a display-bug in excel, the screenshots posted earlier were created by m5, for the following m7 has been used:

Without expanding the input field in the formular bar, the text seems cut after "ipsa":

But after clicking on the yellow marked arrow, is seems as if a blank line follows the text:

Re: Avoid line breaks when exporting long text to Excel

mkeintz — Thu, 29 Jul 2021 05:27:30 GMT

Could the ancient option LINESIZE somehow be influencing PROC PRINT before contents gets passed to the ODS EXCEL facility?

Re: Avoid line breaks when exporting long text to Excel

ChrisNZ — Thu, 29 Jul 2021 10:39:20 GMT

I can't test now, but I'd be astounded as ODS normally ignores this option.

Re: Avoid line breaks when exporting long text to Excel

Ksharp — Thu, 29 Jul 2021 11:32:46 GMT

Or calling @Cynthia_sas . She might have some idea.

Re: Avoid line breaks when exporting long text to Excel

Cynthia_sas — Thu, 29 Jul 2021 14:05:50 GMT

Hi:
The LINESIZE option would not be having an impact on the ODS EXCEL output. PAGESIZE and LINESIZE are listing only options. I don't observe the same issue when I run a test using SAS 9.4M7, so my recommendation is that the original poster should open a track with Tech Support.
Cynthia

Re: Avoid line breaks when exporting long text to Excel

ChrisNZ — Thu, 29 Jul 2021 22:32:48 GMT

I bother the Tech Support guys often enough with actual defects or issues. The behaviour here clearly seems linked to my out-of-date version (still borken in M5 according to @andreas_lds , fixed in M7 according to @Cynthia_sas, and @Tom an @Ksharp must have been on a recent version too). Thank you all!

Re: Avoid line breaks when exporting long text to Excel

Ksharp — Fri, 30 Jul 2021 11:52:36 GMT

I am using SAS University Edition ,it should be M6 .

Re: Avoid line breaks when exporting long text to Excel

Ksharp — Fri, 20 Aug 2021 03:07:23 GMT

@ChrisNZ

Sorry to bring it up.

Maybe style "cellwidth=" could help you .

P.S. I am using SAS9.4M4

ods excel file='c:\temp\reports.xlsx' style=seaside ;
data T ;
  format X $256.;
  X='Sed ut perspiciatis, unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam eaque ipsa, quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt, explicabo.';
proc report data=t nowd ;
define x/display  style(column)={cellwidth=100cm} ;
run;
ods excel close;

Re: Avoid line breaks when exporting long text to Excel

bnawrocki — Mon, 20 Sep 2021 19:07:51 GMT

I'm running M6, and couldn't get it to work when writing a column called Name to Excel.

Name is my SAS field created by concatenating a person's First, Middle and Last Name fields, using CATX function to insert a space between each name. Excel would ALWAYS insert a "line-break" character between either first-and-middle or middle-and-last name! Ugh!

What worked for me was to change the space within " " to a non-breaking-space char (Alt-0160) as the delimiter to CATX function. Luckily, Excel enforces the non-breaking-space character.

name = catx(" ",FIRST, MIDDLE, LAST);

Re: Avoid line breaks when exporting long text to Excel

ChrisNZ — Tue, 21 Sep 2021 04:27:14 GMT

I'd rather avoid special characters. But thanks for the tip. M7 is supposed to come soon.... has been for a while now.... 🙂