The SAS Output Delivery System and reporting techniques

Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

Reply
Regular Contributor
Posts: 170

Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

Why does ODS EXCEL insert line feeds into the data to force lines to wrap when the user doesn't request it?  Can we make ODS EXCEL stop this behavior?

SAS Super FREQ
Posts: 8,866

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

Hi:
  I don't know the answer to your questions, specifically, but Excel Spreadsheet Markup Language uses an XML file and was originated with Office 2003. https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats

  On the other hand, ODS Excel conforms to the Office Open XML standard and creates an archive file with a .XLSX extension that should conform to the Office Open XML standard that was introduced in Office 2007 https://en.wikipedia.org/wiki/Office_Open_XML

  ODS TAGSETS.EXCELXP can ONLY produce the Office 2003 Spreadsheet ML type of XML; while ODS EXCEL can ONLY produce the Office Open XML or .XLSX file.

  What I find is that if my long line wraps, it is because my column width is too small. I can usually control the wrapping with a change in width, as shown below.

wrap_depends_on_width.png

  I find in most ODS destinations that wrapping depends on width, whether we are talking Excel, RTF or PDF.

cynthia

Regular Contributor
Posts: 170

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

[ Edited ]

Cynthia, thanks for your time,

The core of my question deals with lines feeds that are being generated by ODS EXCEL.  I have created this file, d.xlsx.  In it, an XML file named \\xl\sharedStrings.xml resides.  It reads:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="8" uniqueCount="8">
<si></si>
<si><t>Min</t></si>
<si><t>Median</t></si>
<si><t>Max</t></si>
<si><t>edu</t></si>
<si><t>4 YEARS OF COLLEG 4 YEARS OF
COLLEGE</t></si>
<si><t>1 YEAR OF GRAD SC 1 YEAR OF
GRAD SCHOOL</t></si>
<si><t>4 OR MORE YEARS O 4 OR MORE YEARS OF
GRAD SCHOOL</t></si>
</sst>

Note the line feeds here, on certain long lines.  This file was not opened by EXCEL after SAS and ODS Excel created the file.  Now consider the code that produced it:

 

proc format;
  value EDUCLAM
    16 = '4 YEARS OF COLLEG 4 YEARS OF COLLEGE'
    17 = '1 YEAR OF GRAD SC 1 YEAR OF GRAD SCHOOL'
    18 = '2 YEARS OF GRAD S 2 YEARS OF GRAD SCHOOL'
    19 = '3 YEARS OF GRAD S 3 YEARS OF GRAD SCSCHOOL'
    20 = '4 OR MORE YEARS O 4 OR MORE YEARS OF GRAD SCHOOL'
  ;
run;

data nowrap;
  infile datalines;
  input edu;
  format edu EDUCLAM.;
datalines;
16
17
20
;
run;

ods excel file="d.xlsx"
style=Sasweb;
  PROC TABULATE DATA=WORK.NOWRAP ;
    VAR edu/ style=[tagattr='wraptext:no' ];
    TABLE edu,(min Median max)*F=EDUCLAM.;
  run;
ods excel close;

No line feeds...   It is not one of the principles of SAS output that the data that is provided for output is presented for output without modification.  Yet this is happening, added linefeeds appear.  I don't think it should be doing this, or specifically, it should do this only if the user specifically request this behavior.  Is this grounds for a more formal error report --- in your opinion?  thanks again

 

SAS Super FREQ
Posts: 8,866

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

Hi:

  When I submit code using your format and slightly different style overrides, using WIDTH= on the TABLE statement for the data cells, this is what I see in the XML file:

my_sharedstrings.png

 

  I do not observe the line feed issue you illustrated.

 

  The style override on the VAR statement,  impacts the header cell -- so the header cell with Min, Median and Max was never in any danger of needing WRAPTEXT, but those were the cells you were conrolling with your override on the VAR statement. When you put a style override on a TABLE statement, it impacts the data cells.  Here's what I did that produced the above example in the shared strings file:

width_controls_text_wrap.png

Although I don't understand your need to put long text strings in data cells, when I used WIDTH in the right place, I did not observe wrapping in the XML or in the sheet when opened with Excel. The bottom line is that what is showing above in blue background with white text is a column header or row header cell -- those are changed on the class or var statement with style overrides. Or, as shown in my example below, statistic header cells are changed on the KEYWORD statement. What is in black font with white background above is a data cell inside the table and to impact those cells, you usually put your style override on the TABLE statement.

 

In my code, I did not use TAGATTR with WRAPTEXT -- I just made the width wide enough to accomodate the long value. Yes, it took some fiddling because even when I specify inches, Excel seems to convert my inches to some internal calculation that is opaque to me.

 

I find that WIDTH= works for me in most cases. I don't think that TAGATTR with WRAPTEXT by itself will be sufficient. If you need to understand the internal workings or this example is not sufficient to illustrate a workaround, then you might need to open a track with Tech Support.

 

Here's an example of the difference with style overrides in the var and class statements vs the TABLE statement. I just used colors because those are easy to spot and link the statement to the piece of the table that is impacted.

style_override_tab.png

 

cynthia

Regular Contributor
Posts: 170

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

[ Edited ]
Posted in reply to Cynthia_sas

I see two ways wrap text is being implemented

1. using Excel's cell attribute "WrapText", defined in the XML Schema, which is turned on or off by STYLE and TAGATTR.

2. ODS EXCEL replaces space characters with lines feeds, if columns are not requested to be wide enough.

 

This makes ODS Excel a pain-- My opinion.  I would like SAS to use the latter and not the former, and I don't want to play "column width cowboy".  (sorry, _too_ sardonic? I hope you can laugh at that. ) This is the second time I've encountered this and I guess I can use tagsets.excelxp.  again thanks for your time.

 

New Contributor Z01
New Contributor
Posts: 2

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

[ Edited ]

Hello

I'm writing because I have the same issue as the person that asked the question and because I also saw that it was asked recently. Actually, for us the solution with changing width is in our context not a doable solution. The reason is that I am trying already to insert a PROC REPORT after another PROC REPORT. I can thus not change width because the width is already given by the previous (style-wise dominant) PROC REPORT. It would thus be a good idea to eventually implement the second possibility - particularly if it is something that also other users could use.

 

And why would I need a PROC REPORT after another PROC REPORT? Well, let's say this is already an attempt of workaround because something else was already not possible with SAS (Footnotes limited to 256characters). So I will now look for a workaround to the workaround.

 

Kind regards

 

Edit: By the way: Same issue with PROC PRINT.

 

 

 

 

SAS Super FREQ
Posts: 8,866

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

[ Edited ]

Hi, footnotes and titles are not impacted by the procedure you use. They are impacted in the LISTING destination by LINESIZE, but that would NOT apply to HTML or ODS EXCEL, for example.
I have a program that displays title and footnote text longer than 400 characters. I will post it for you to test.
cynthia

 

program:

%let word1 = %str(abcd efgh ijklmn opqrs tuv wxyz------ ----- ----01);
%let word2 = %str(abcd efgh ijklmn opqrs tuv wxyz------ ----- ----02);
%let word3 = %str(abcd efgh ijklmn opqrs tuv wxyz------ ----- ----03);
%let word4 = %str(abcd efgh ijklmn opqrs tuv wxyz------ ----- ----04);
%let word5 = %str(abcd efgh ijklmn opqrs tuv wxyz------ ----- ----05);
%let word6 = %str(abcd efgh ijklmn opqrs tuv wxyz------ ----- ----06);
%let word7 = %str(abcd efgh ijklmn opqrs tuv wxyz------ ----- ----07);
%let word8 = %str(abcd efgh ijklmn opqrs tuv wxyz------ ----- ----08);
** each macro variable is 50 characters. So using all 8 macro variables will;
** generate a string that is over 400 characters long in the title and the footnote;

ods listing close;
option noquotelenmax;
ods html file='c:\temp\long_text.html';
ods excel file='c:\temp\long_text.xlsx'
    options(embedded_titles='yes' embedded_footnotes='yes');
title1 "The title";
title2 "&word1 &word2 &word3 &word4 &word5 &word6 &word7 &word8 #end#";
  
footnote1 "The footnote";
footnote2 "&word1 &word2 &word3 &word4 &word5 &word6 &word7 &word8 #end#";
proc print data=sashelp.class(obs=2);
run;

ods html close;
ods excel close;
title; footnote;

 

output (note that you see the #end# at the end of the title and footnote in HTML and in ODS EXCEL output): 

title_footnote_gt_256.png

New Contributor Z01
New Contributor
Posts: 2

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

Posted in reply to Cynthia_sas

Hi Cynthia,

 

Thank you very much for your reply. Well the issue with the length of linesize was a bit different. It is true that the output was OK within SAS but also in the excel. But if the same code was executed twice, then the excel-file gave an error while opening ("file damaged - do you want to repair it?" - something like that). So it was very weird because restarting SAS, executing the code had solved that issue. By the way, no warnings withing SAS towards that phenomenon.

 

What I see now in your code is

option noquotelenmax; 

 This is indeed something I have not tried. I have not tried it yet but this might eliminate the issue.

 

My solution (as the project was in a hurry) was now to use many ODS TEXT. The solution is not very elegant (among others needing an empty PROC PRINT to display the ODS TEXT and to cut the length of the lines manually) but it worked. There is also the possibility to put style in ODS Text.

 

But I will definitively have to come back to that issue, and so as soon as possible I will check the possibility you provided.

 

Kind regards!

SAS Super FREQ
Posts: 8,866

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

Hi:
You have to close Excel between runs. Excel cannot be holding the file open for the second run, or else you will get an error. SAS will not write over a file that Excel is holding open. That's the only reason I can think of for getting the file damaged error. What did your Log say?
cynthia
Ask a Question
Discussion stats
  • 8 replies
  • 463 views
  • 3 likes
  • 3 in conversation