ExcelXP tagset - How to keep hard returns in a text field?

[EStrom]

I'm trying to push text to an XML file via the ExcelXP tagset, but embedded carriage returns in text fields are getting stripped out.  Is there a method I can use to retain the carriage returns?

I know that the XML file is capable of storing the hard returns because I can manually put them in (by typing alt-Enter, just like a normal Excel file).  If I then inspect the XML data that is saved, I see that the hard return is getting stored in the XML file as the value "
" (no quotes).  I have tried using the prxchange function to replace existing characters with the "
" string, but this fails because the ampersand gets stored as &amp.

Is there another way I can retain the carriage returns?

[Cynthia_sas]

Hi:

  if you are going to use 
 as the carriage return/linefeed, then you have to tell SAS and ODS not to help you out. Normally, they try to help you by "protecting" that & and coding it as & -- which for what you want is not a help.

  If you know that 
 is correctly inserted in your text string, then in PRINT or REPORT, you can do this (let's assume you variable is called COMMENT):

var COMMENT / style(data)={protectspecialchars=off}; (PROC PRINT)

define COMMENT / style(column)={protectspecialchars=off}; (PROC REPORT)

cynthia

ps...if 
 doesn't show up correctly, it is ampersand-sharp sign-10-semicolon

[EStrom]

This didn't quite work the way I hoped.

The protectspecialchars setting did allow the "
" string to pass through without the automatic re-coding of the ampersand to &amp.

However, when I open the XML file in Excel, the cell shows a square box where the hard return should be.  If I open the cell in Excel with F2, it correctly displays the hard return (and no box).  If I then hit enter after F2 to save the cell, the cell now displays in the correct format (with hard return and no box).  So basically, it all looks weird unless I open and close every cell manually.

It is supposed to look like this:

bbb

BBB

But straight from SAS it looks like this:

bbb{small box}BBB

Inspection of the XML doesn't help (me anyway - I don't really read XML).

The individual line where the data value is stored looks like this straight from SAS:

<Cell ss:StyleID="data__l" ss:Index="1"><Data ss:Type="String">bbb&#10;BBB</Data></Cell>

After doing F2, enter and re-saving, the XML line looks like this

<Cell ss:StyleID="s66"><Data ss:Type="String">bbb&#10;BBB</Data></Cell>

There may be other changes in the XML file; I'm not certain.

[data_null__]

Consider these two lines from the tagset template and how they might be modified to produce the desired result.

map = '<>&"';
mapsub = '/&lt;/&gt;/&amp;/&quot;';  

and the answer is

    map = '3C3E26220A'x;
    mapsub = '/</>/&/"/
';