topic Re: Help with ODS EXCEL in ODS and Base Reporting

Help with ODS EXCEL

PaigeMiller — Wed, 10 Jun 2015 18:54:17 GMT

I am having a small problem with ODS EXCEL (yes, I know it is experimental), where parts of the letters in the first row are cut off, and I can't find an option in either ODS EXCEL or PROC REPORT to modify this behavior. Attached is a screen capture, note that in columns D, E and H, the tops of the letters are cut off. Since these reports go to engineers and managers, I'd like to fix it before a manager stops by my office and tells me to fix it. Any ideas?

Re: Help with ODS EXCEL

Reeza — Wed, 10 Jun 2015 19:11:05 GMT

Are you manually setting the row height?

Re: Help with ODS EXCEL

PaigeMiller — Wed, 10 Jun 2015 19:18:51 GMT

No, I am not because unlike ODS TAGSETS.EXCELXP, the row height option is disabled and does nothing in ODS EXCEL. Although there appears to be a new option ABSOLUTE_ROW_HEIGHT, this isn't what I want, as it sets every row height to the same value. I was under the impression that the proper setting of Row Heights would happen automatically, but either I am mistaken or it isn't working, which is why I would like to set the height of row 1 myself, if only I could find out how.

Re: Help with ODS EXCEL

Steelers_In_DC — Wed, 10 Jun 2015 19:56:02 GMT

I'm not able to test this right now but see if this works in your options line:

autofit_height = 'yes'

Re: Help with ODS EXCEL

PaigeMiller — Wed, 10 Jun 2015 19:59:57 GMT

That command doesn't apply to ODS EXCEL, according to http://support.sas.com/resources/papers/proceedings14/SAS177-2014.pdf, which implies that there's no need for such a command as Excel handles this properly.

Re: Help with ODS EXCEL

Ksharp — Thu, 11 Jun 2015 13:30:42 GMT

What proc are you using to create EXCEL file ?

proc report ?

Could change cell height in proc report via STYLE ?

proc report style(header)={ cellheight=20cm } ?

Xia Keshan

Re: Help with ODS EXCEL

PaigeMiller — Thu, 11 Jun 2015 13:48:14 GMT

Yes, I am using PROC REPORT.

Your answer helps in the sense that I can specify a cellheight and the problem goes away, as long as the text doesn't need a bigger cell height — and since I'm working with text that can change dynamically, it would still be better if the height of the cell was fit automatically to the amount of text present, which is what I really want, and which is what I thought ODS EXCEL would provide.

So thank you for this answer.

Nevertheless, , I have a related question.

In SAS, I press F1 and call up the SAS Help and Documentation, and then do a search for cellheight. Guess what, the search fails to find any reference to cellheight, so where is the documentation on cellheight? What other style elements are available that don't appear in SAS help? Who can point me to a full list of possible style elements I can use with PROC REPORT?

Re: Help with ODS EXCEL

Ksharp — Thu, 11 Jun 2015 13:59:28 GMT

HoHo, Cynthia is here and She is good at it .

Message was edited by: xia keshan

Re: Help with ODS EXCEL

Ksharp — Thu, 11 Jun 2015 14:12:29 GMT

A workaround I can think is padding a blank character like '09'x before the label of variables .

And also try this style:

proc report style(header)={ cellpadding=100 }

Xia Keshan

Re: Help with ODS EXCEL

PaigeMiller — Thu, 11 Jun 2015 14:29:34 GMT

Cellpadding seems to have no effect on the first row (headers), which is also what I understand from the appropriate parts of SAS Help

Base SAS(R) 9.4 Procedures Guide, Third Edition

Re: Help with ODS EXCEL

Ksharp — Fri, 12 Jun 2015 12:39:13 GMT

Try to put it on a variable .

define x/..... style={ cellpadding=100cm }

Re: Help with ODS EXCEL

Cynthia_sas — Fri, 12 Jun 2015 13:38:17 GMT

Hi:

Putting cellpadding on a DEFINE statement for a particular variable or item will not work. CELLPADDING is a table-level change. You can test this out for yourself by running the code below. See attached output. Only #2 changes the padding inside each table cell. Code for #3 and #4 have no impact on PROC REPORT output.

Cynthia

ods html file='cellpadding.html';

proc report data=sashelp.class(obs=2) nowd;
title '1 cellpadding default';
run;

proc report data=sashelp.class(obs=2) nowd
style(report)={cellpadding=10px};
title '2 cellpadding change for table';
run;

proc report data=sashelp.class(obs=2) nowd
style(header)={cellpadding=10px};
title '3 try cellpadding on header';
run;

proc report data=sashelp.class(obs=2) nowd;
title '4 try cellpadding on column';
define name / style(column)={cellpadding=10px};
run;
ods _all_ close;

Re: Help with ODS EXCEL

PaigeMiller — Fri, 12 Jun 2015 16:26:43 GMT

An e-mail from SAS tech support indicates this is a bug that will be fixed when ODS EXCEL is made production (right now it is experimental)