The SAS Output Delivery System and reporting techniques

Help with ODS EXCEL

Reply
Trusted Advisor
Posts: 1,783

Help with ODS EXCEL

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?


Binary_AA045_WFR_limyield.jpg
Super User
Posts: 19,038

Re: Help with ODS EXCEL

Are you manually setting the row height?

Trusted Advisor
Posts: 1,783

Re: Help with ODS EXCEL

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.

Valued Guide
Posts: 858

Re: Help with ODS EXCEL

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

autofit_height = 'yes'

Trusted Advisor
Posts: 1,783

Re: Help with ODS EXCEL

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.

Super User
Posts: 9,856

Re: Help with ODS EXCEL

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

Trusted Advisor
Posts: 1,783

Re: Help with ODS EXCEL

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?

Super User
Posts: 9,856

Re: Help with ODS EXCEL

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

Message was edited by: xia keshan

Super User
Posts: 9,856

Re: Help with ODS EXCEL

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

Trusted Advisor
Posts: 1,783

Re: Help with ODS EXCEL

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

Super User
Posts: 9,856

Re: Help with ODS EXCEL

Try to put it on a variable .

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

SAS Super FREQ
Posts: 8,815

Re: Help with ODS EXCEL

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

cp.png

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;

Trusted Advisor
Posts: 1,783

Re: Help with ODS EXCEL

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)

Ask a Question
Discussion stats
  • 12 replies
  • 977 views
  • 5 likes
  • 5 in conversation