topic Re: ODS EXCEL worksheet header truncation WARNING: in ODS and Base Reporting

ODS EXCEL worksheet header truncation WARNING:

data_null__ — Mon, 24 Jun 2024 14:55:51 GMT

I have an application that use ODS EXCEL to "FLOW" a text string into a cell with specified width, ODS EXCEL inserts linefeed character '0A'x into the string to produce a new line within each cell. I then read the XLSX back into a data set and count the number of linefeeds.

When I have long titles or footnotes, I get the following warnings.

WARNING: Worksheet header is too long. Truncation will occur.
WARNING: Worksheet footer is too long. Truncation will occur.

This application does not need titles or footnotes, but I don't want to turn them off with TITLE; FOOTNOTE; statements because I can't easily turn them back on.

I've tried using PROC PRINTTO to send the log to NUL and that works ODS and PROC statements, NOTES etc. disappear from the log but NOT the warning.

ODS EXCEL has an option, MSG_LEVEL='NO NOTES', that looked promising but does not suppress the warnings.

I think if I could redirect STDERR that would work but I cannot figure that.

Re: ODS EXCEL worksheet header truncation WARNING:

Tom — Mon, 24 Jun 2024 15:23:10 GMT

It is not clear from your description what code you are running and where in the process the warnings are being generated.

If it is in a PROC IMPORT step and the cause is title lines in the spreadsheet then I would suggest skipping the title lines when doing the PROC IMPORT.

Something like this:

title1 'First header line is very long and too long to be a varible name';
filename xlsx temp;
ods excel file="%sysfunc(pathname(xlsx))" options (embedded_titles='yes');
proc print data=sashelp.class noobs;
run;
ods excel close;
proc import file=xlsx dbms=xlsx out=test replace;
 range="$A3:";
run;
proc print;
run;

If you want to see how the titles and footnotes defined look at the DICTIONARY table (view?) called TITLES, which you can also see as SASHELP.VTITLE.

proc print data=sashelp.vtitle;
run;

Result

Re: ODS EXCEL worksheet header truncation WARNING:

data_null__ — Mon, 24 Jun 2024 15:42:48 GMT

Sorry @Tom I should have included example. This shows the problem and message from ODS EXCEL. I am not embedding titles or footnotes. And I don't need the worksheet headers or footers.

49         title1 'First header line is very long and too long to be a varible name'
50               'First header line is very long and too long to be a varible name'
51               'First header line is very long and too long to be a varible name'
52               'First header line is very long and too long to be a varible name'
53               'First header line is very long and too long to be a varible name';
54         filename xlsx temp;
55         ods excel file="%sysfunc(pathname(xlsx))" options (embedded_titles='NO');
56         proc print data=sashelp.class noobs;
57         run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds
WARNING: Worksheet header is too long.  Truncation will occur.
58         ods excel close;

Re: ODS EXCEL worksheet header truncation WARNING:

Tom — Mon, 24 Jun 2024 16:04:53 GMT

So SAS has some inconsistency in what it allows in title lengths. The maximum that it will surface in SASHELP.VTITLE is 256 (255?) characters. But it seems to be able to use more.

The ODS EXCEL error when trying to put the titles in the header of the spreadsheet is seems to trigger at that 256 length. So that is either an excel limit (or a limit that ODS EXCEL thinks exists).

Note that if you just change the EMBEDDED_HEADERS option on the ODS EXCEL destination to "YES" then you will not get the warning. So as long as you know how many title lines there are you could probably work out a way to make the file and read it back in.

Re: ODS EXCEL worksheet header truncation WARNING:

data_null__ — Wed, 26 Jun 2024 14:30:00 GMT

I change my titles macro to put the title and footnote statements in a temporary file that I can %include as needed. Now I can use

TITLE; FOOTNOTE;
*do the EXCEL bits.;
%inc TITLES;

No warnings.

@Tom Thanks for your comments and interest in this topic.

Re: ODS EXCEL worksheet header truncation WARNING:

A_Kh — Mon, 01 Jul 2024 13:44:46 GMT

I wonder if the system option noquotelenmax works here too.