Recurring question: Automatically Adjust Row Height in Excel

[ChrisNZ]

Here is a table with variables that contain values of random lengths and random number of lines. 
Q: Is there a way to export this to Excel from SAS so all values are visible inside a cell?

No wrapping, one line per value, cell width not that important.

It seems that ever since option autofit_height  was removed, nothing works?
The EOL delimiter can be changed

data HAVE;
  x = catx( '0D0A'x, '0000000000', '4445566778','9999999999','9991123456'); 
run;

ods excel file="&wdir\print.xlsx" options(sheet_name='Test');          
  proc print data=HAVE noobs ;
  run;
ods excel close;

Setting cellwidth to force line wrapping as sometimes suggested does not look like a solution since it breaks lines randomly rather than where required.

[Ksharp]

Chris,

You could try option "tagattr=“ to avoid  wrapping  a line when you want to outpu an excel file.

Here is an example:

data HAVE;
  x = catx( '0D0A'x, '0000000000', '4445566778','9999999999','9991123456'); 
run;

ods excel file="c:\temp\print.xlsx" options(sheet_name='Test');          
  proc report data=HAVE nowd ;
  columns x;
  define _all_/display style={tagattr="wrap:no"};
  run;
ods excel close;

[ChrisNZ]

Thank you @Ksharp !

Sadly this doesn't expand the row height. 

Excel actually behaves weirdly (this might be a company setup issue):

When I open the file, all values are in one row. Then I click on the cell, then on the formula bar and the cell changes to only display the top row.

In any case I cannot see the list of values because the row height is still the default height.

[Ksharp]

Chris,

Could you adjust it by hand ? Just click this button when you tap a cell.

[ChrisNZ]

> Could you adjust it by hand ? Just click this button when you tap a cell.

@Ksharp Thank you for the suggestion!
This doesn't always work, and we don't really want to create a sheet that is not ready-to-use. Users should be able to see results when they open the file. 

[LinusH]

With some trial and error and AI conversation, I got all lines visible from your example:

data HAVE;
  x = catx( '0A'x , '0000000000', '4445566778','9999999999','9991123456'); 
run;

ods excel file="print.xlsx" options(sheet_name='Test' 
												 flow="Tables" 
												 row_heights="0,0,0,0,0");          
  proc print data=HAVE noobs ;
  	var x / style(column)={width=2.5cm
                           vjust=top 
                           tagattr="wrap:yes"
                       };
  run;
ods excel close;

But as you can guess, the setting of width= needs to be correlate with the actual length of your strings, which probably will make this hard to use in every day applications....?

[ChrisNZ]

@LinusH  Thank you for the suggestion.
That works! Having to guess a width for each column is a pain, and the resulting row height seems to always be too high. But at least all the values are visible. 👍

[Tom]

What happens if you ask ODS itself to split the lines of text in the cells by using an ODS escape sequence?

data HAVE;
  x = catx( '(*ESC*)n' , '0000000000', '4445566778','9999999999','9991123456'); 
run;
filename xlsx '~/example.xlsx';
ods excel file=xlsx
 options(sheet_name='example' 
				flow="Tables" 
				)
;          
proc print data=have noobs;
run;
ods excel close;

That seems to come close when I open the XLSX file in Numbers application.

[ChrisNZ]

@Tom Thank you for the suggestion.

This works well and is rather clean: Few SAS options, good layout in Excel.

I tried using option split= but not the ODS version, which turns out to be the one SAS and/or Excel handle the best, at the cost of some data pollution (the data is less human friendly, not a deal breaker here).

The row height is not always perfect, but that's probably an Excel issue.

I wish option autofit_height  was still available (and I am curious to know why it was removed), but in the meantime this seems to be the best workaround.