Hello,
I now own a report that writes from datasets that can be small or large. The report is setup as a macro program and conditionally writes to Excel, PDF, or CSV files.
For excel file generation, we recently changed the ODS destination from TAGSETS.EXCELXP to EXCEL.
We applied this change across the board, so that our output xlsx files would open without the error that xls files generated from TAGSETS.EXCELXP produces.
We noticed after this change that some reports generate an error:
WARNING: A table is too large to fit in the available space. The table headers and footers have been suppressed so that at least one row can be printed.
I can get this report to write to CSV or PDF.
The SAS KB article http://support.sas.com/kb/56/985.html is a bit light on solutions. I tried doing by grouping, and get a result I don't want (separate tables within a worksheet).
More importantly, using the data set option in the proc report call and limiting to a single observation still results in the error.
example: proc report data=blah (obs=1) &reportoptions.;
Therefore, I am curious if SAS ODS EXCEL has a maximum allowed cell content length or something similar?
What supports this idea is that when I remove the column from the report, I can report everything.
The column is set elsewhere in a data step to LENGTH=$10000 [ten thousand]
One of the rows contains a string of 7344 characters...
So I played with this using a data step attribute to change the length of that column and I find that LENGTH=$2900 is the max I can do - beyond 2900 characters, I get that warning.
Seeing that the character length seems to be triggering this warning, I think it is outside the scope of solutions proposed by the SAS KB article.
I did not go back and try this with TAGSETS.EXCELXP to see if there is a limit or any kind of automatic truncation. What I do know is the error did not happen in the past with TAGSETS.EXCELXP, but we will not use that because of the warning message it generates when opening the resulting .xls file.
This variable is set to such a long length because of that problem everyone has had to deal with: row concatenation. Elsewhere, we concatenate values across many rows to generate a very long string that is dumped here. The list of values is so long, it is arguably useless. Regardless if the content is useful, I want to know how to get ODS EXCEL to write these data out.
Does anyone know if ODS EXCEL has a defined maximum character length for variables (cells) or is this a bug? I understand what we are dumping into this column is effectively useless...
Click image to register for webinar
