Avoid line breaks when exporting long text to Excel

[ChrisNZ]

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?

[andreas_lds]

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.

Spoiler

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;

[Ksharp]

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;

[johnsville]

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 !

[Tom]

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

[johnsville]

9.4 (TS1M4)

[Tom]

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.

[ChrisNZ]

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.

[andreas_lds]

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:

[mkeintz]

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

[ChrisNZ]

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

[Ksharp]

Or calling @Cynthia_sas  . She might have some idea.

[Cynthia_sas · Accepted solution]

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

[ChrisNZ]

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! 

[Ksharp]

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