topic Re: ExcelXP Variable Size in ODS and Base Reporting

ExcelXP Variable Size

OS2Rules — Wed, 27 Oct 2010 16:18:46 GMT

Hi All:

Just a quick question - is the variable size limited to 96 bytes when using ExcelXP?

I have a large text variable that I need to include but the log has:

ERROR: The width of Description is not between 1 and 96. Adjust the column width or linesize.

My column width is 100 and linesize is 200;

I've never come across this one before.

Thanks in advance.

Re: ExcelXP Variable Size

Tim_SAS — Wed, 27 Oct 2010 16:24:16 GMT

That message looks like a PROC REPORT message that is issued when a column in the report exceeds the listing linesize. If you want only the ExcelXP output and don't need the listing output, turn the listing off:
[pre]
ods listing close;
...stuff...
ods listing;
[/pre]

Re: ExcelXP Variable Size

Cynthia_sas — Wed, 27 Oct 2010 18:39:43 GMT

Hi:
As Tim explained, this happens all the time when I have wide character variables that are longer than the LISTING linesize. This Tech Support note
http://support.sas.com/kb/18/518.html

outlines the issue. This issue has been around since SAS 8 -- so you're lucky you haven't run into it before!

cynthia

Re: ExcelXP Variable Size

OS2Rules — Fri, 29 Oct 2010 13:43:44 GMT

All:

Thanks for the great help. Never would have thought to stop the listing.

Turns out the text field contains a ton of special characters so either ExcelXP or Excel itself choke on random records. I have no way of telling which record or position causes the problem.

I finally had to run a translate on the text field and keep only alpha and numeric characters (translate everything else to spaces) to get all the data on the report.

Just one more thing - since this is a text field and can be up to 1024 chars, the text wraps in the cell - is there any way to auto-adjust the height of the cell so that the text can be read without manually changing the cell height?

Re: ExcelXP Variable Size

Cynthia_sas — Fri, 29 Oct 2010 15:21:29 GMT

Hi:
Excel and row heights can be tricky. If you search for previous forum postings, this has come up before. I believe the relevant sub-option is the row_heights suboption -- however, it only has a way to set a row height for ALL the table cells -- not just one row or one column.

I don't know whether this is an instance that a CALL DEFINE in PROC REPORT would be able to specify a height for a specific row or not. But perhaps if you play around with row_heights and the autofit_height sub-options, you will get close to what you want.

cynthia