PROC ODSTEXT, line wrapping and 139

[PhilC]

I was trying to output a long string using PROC ODSTEXT, but the lines wrapped in Excel.  I tried using "tagattr=wraptext:no",  

no dice

Desperate -- I started varying the length of the string... I found that there was a threshold.

ods excel
  file=  "&Folder\wraptext.xlsx";

  proc odstext;
    p "Discharges from (screen) and Investigation Records" 
       /style=[tagattr='WrapText:0' fontsize=14pt font_weight=bold];
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners wi";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with a";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with appro";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with appropria";
    p " ";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with ap";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with app";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with appr";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with appro";
run;

  ods excel close;

The magic number is 139.  (???)  So why?  .

[Cynthia_sas]

Hi:
I believe that the TAGATTR specification was intended for use with ODS TAGSETS.EXCELXP. I think that starting in 9.4M4, the ODS EXCEL destination (which you're using) supports a FLOW= suboption that tells ODS EXCEL whether or not to insert carriage returns into long text strings based on the default column width that it wants to set.

Cynthia

[PhilC]

If 139 then is based on the column width then its way off, and too long.  The above example uses the default Excel column width and 139 characters fill ~10 times the length of the cell.

So I identify two different kinds of line wrapping going on here. 

1. First, there is the line wrapping ODS EXCEL implements (fixed with the option Flow), this inserts carriage returns in to the data.
2. Second, the line wrapping done, or not done, because of the specific Excel, XML schema level, tag and attribute <Alignment .. ss:Wraptext=...> set to "1" or "0".

But, I think - it seems, that ODS Excel is placing a XML tag and attribute <Alignment .. ss:WrapText="1"> (method #2) conditional on the number of characters. Which, to me, is frustrating ... but not as much as the insertion of carriage returns (Method #1).

I really was hoping that tagattr=WrapText would be a supported feature of ODS EXCEL.  It's as basic a formatting option as "bold" in my opinion.

And thanks for chiming in Cynthia.  And thanks for reading to my rants, future and past.

[PhilC]

You're right, it is inserting a carriage return  but its also setting the WrapText="1" attribute at the same time.  

I was confused, but I'm still confused the same.

So much about ODS makes sense to me, but not the decisions surrounding text wrapping in ODS Excel.

[PhilC]

Cynthia,

I'm also sorry to say the Flow option actually forces line wrapping, that is the second kind.  Where it does stop the inserting of carriage returns (method #1) it sets WrapText (method #2) for all cells its applied to.

%MACRO Flow_option(option,suffix);
%if "&SUFFIX"eq"" 
  %then %let suffix=&option;

ods excel
  file=  "&Folder\wraptext &suffix..xlsx"
  options( FLOW="&option");

  proc odstext;
    p "Discharges from (screen) and Investigation Records" 
       /style=[tagattr='WrapText:0' fontsize=14pt font_weight=bold];
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with appropriate HIPAA-compliant business agreements.";
    p " ";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners wi";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with a";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with appro";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with appropria";
    p " ";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with ap";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with app";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with appr";
    p "This file contains HIPAA protected healthcare information.  It is intended for use within this agency and with authorized partners with appro";
run;

  ods excel close;
%MEND;

/*Examplw with...*/
%Flow_option(DATA);
%Flow_option(HEADERS);
%Flow_option(ROWHEADERS);
%Flow_option(TABLES);
%Flow_option(TEXT);
%Flow_option(A1:A30,A1toA30);

I am thinking about how to put this in a SAS Ballot.

[MaryA_Marion]

I am finding it effective to simply print in HTML when finished processing in SAS. I would like to write a paragraph after a procedure was run explaining the results. I'm finding I need to do an ods text = statement for each line of prose output. That way I can control the font and  font size and be sure the prose stops soon enough so printed output will be sure to include it. I read your response and the use of the FLOW option with ods Excel destination. Is there something similar I can do with automatic ods HTML output? I am yet to get this to work successfully. Thank you.
MM

[PhilC]

Hi, So you mentioned ”ODS TEXT=". But I am speaking of "PROC ODSTEXT".
Your solution seems to be a great use PROC ODSTEXT. Link:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsproc/p1tfjsx3ezqhacn1gqqsxqii7lpv.htm

You need a recent update of SAS 9.4 for it to work. I forget when it was
added to SAS 9.3 or 9.4. Although I do not use ODS HTML very much any
more, I would be surprised if you could not use a PROC ODSTEXT with your
HTML output.

[MaryA_Marion]

Thanks for replying. I'm doing better with proc odstext. It adjusts itself to whatever output type you specify. For example, it wraps very well in pdf files. MM

[xxformat_com]

Hi,

If you test this example, you will notice that 128 characters are used (width=100%).

Adding one extra character actually create a line break character. So 130 characters in total. Add the len() Excel function to see it.

So to double the width, we can use the width=200% style attribute.

Having said that, I'm not sure where 128 limits is defined. Could it be in the style template? I haven't see it there.

ods excel file="&xxtest./reporting/ods_excel_test.xlsx";


proc odstext;
	p 'abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-123456';
	p 'abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-1234567';
	p 'abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-12345678';
	p 'abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-123456789';
	p 'abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-abcdefghi-123456789-';
run;


ods excel close;

ps. ods excel options(flow='text') does not help in this example.