The SAS Output Delivery System and reporting techniques

ExcelXP Variable Size

Reply
Super Contributor
Posts: 358

ExcelXP Variable Size

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.
Super Contributor
Posts: 394

Re: ExcelXP Variable Size

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]
SAS Super FREQ
Posts: 8,868

Re: ExcelXP Variable Size

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
Super Contributor
Posts: 358

Re: ExcelXP Variable Size

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?
SAS Super FREQ
Posts: 8,868

Re: ExcelXP Variable Size

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
Ask a Question
Discussion stats
  • 4 replies
  • 770 views
  • 0 likes
  • 3 in conversation