BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yellowcat
Fluorite | Level 6

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...

1 ACCEPTED SOLUTION
6 REPLIES 6
Reeza
Super User
What version of SAS do you have?

proc product_status; run;

Check the log, you're looking for SAS 9.4 TS1M4+ ideally.
yellowcat
Fluorite | Level 6

Thanks for giving me the proc to get the version!

I have ...

from the log as soon as I launch sas: 9.4 (TS1M2)

 

from proc product_status; run;

For SAS/GRAPH ...
Custom version information: 9.4_M2
For SAS/FSP ...
Custom version information: 9.4_M2
For SAS/AF ...
Custom version information: 9.4_M2
For SAS/IML ...
Custom version information: 13.2
For SAS/SHARE ...
Custom version information: 9.4
Image version information: 9.04.01M0P061913
For SAS/CONNECT ...
Custom version information: 9.4_M2
For SAS/IntrNet ...
Custom version information: 9.4_M2
For SAS/Secure 168-bit ...
Custom version information: 9.4_M1
For High Performance Suite ...
Custom version information: 2.2_M1
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M2
For SAS/ACCESS Interface to ODBC ...
Custom version information: 9.4_M2

Reeza
Super User
Unfortunately ODS Excel isn't production until 9.4 M3 so you're using a pre-production version that is buggy. Upgrades are included in your license and you really only need one installation upgraded....but I can see staying with ODS TAGSETS until you're fully upgraded.
yellowcat
Fluorite | Level 6

@Reeza - thanks for the quick answer! Is that documented somewhere in a KB article or similar? I'd like to show Important People that information and see if I can push for an update. 

yellowcat
Fluorite | Level 6

@Reeza thanks much for the links!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3775 views
  • 0 likes
  • 2 in conversation