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 &.
Is there another way I can retain the carriage returns?
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
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 &.
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 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 BBB</Data></Cell>
There may be other changes in the XML file; I'm not certain.
Consider these two lines from the tagset template and how they might be modified to produce the desired result.
map = '<>&"';
mapsub = '/</>/&/"';
and the answer is
map = '3C3E26220A'x;
mapsub = '/</>/&/"/ ';
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.