topic Re: PROC ODSTEXT, line wrapping and 139 in ODS and Base Reporting

PROC ODSTEXT, line wrapping and 139

PhilC — Tue, 07 May 2019 20:08:49 GMT

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

Re: PROC ODSTEXT, line wrapping and 139

Cynthia_sas — Tue, 07 May 2019 22:35:43 GMT

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

Re: PROC ODSTEXT, line wrapping and 139

PhilC — Wed, 08 May 2019 12:10:56 GMT

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.

First, there is the line wrapping ODS EXCEL implements (fixed with the option Flow), this inserts carriage returns in to the data.
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.

Re: PROC ODSTEXT, line wrapping and 139

PhilC — Wed, 08 May 2019 12:43:46 GMT

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.

Re: PROC ODSTEXT, line wrapping and 139

PhilC — Wed, 08 May 2019 13:02:57 GMT

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.

Re: PROC ODSTEXT, line wrapping and 139

MaryA_Marion — Sun, 01 Aug 2021 18:57:12 GMT

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

Re: PROC ODSTEXT, line wrapping and 139

PhilC — Mon, 02 Aug 2021 00:47:17 GMT

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.

Re: PROC ODSTEXT, line wrapping and 139

MaryA_Marion — Sat, 07 Aug 2021 16:36:16 GMT

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

Re: PROC ODSTEXT, line wrapping and 139

xxformat_com — Tue, 09 Nov 2021 12:35:50 GMT

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.