topic Re: How do I use the ODS escapechar with the excelXP tagset? in ODS and Base Reporting

How do I use the ODS escapechar with the excelXP tagset?

deleted_user — Fri, 16 May 2008 01:23:57 GMT

I would like to have a cell which contains a hard return. However, when I specify a newline using the ODS escapechar it generates malformed XML. For example:

data test;
x = 'aaa^nbbb';
run;

ods _all_ close;
ods escapechar="^";
ods tagsets.excelXP file="C:\temp\test.xls" style=styles.sasweb
options (sheet_name= "test");

proc print data=WORK.test noobs;
var x;
run;

ods _all_ close;
ods listing;

Thank you.

Re: How do I use the ODS escapechar with the excelXP tagset?

deleted_user — Fri, 16 May 2008 11:26:46 GMT

using [pre] proc print label split='*' ;[/pre]would allow you to define where a "hard return" should go into the column headers.

Try that on a test basis.

If you need the hard return in a data cell, you might be able to insert corresponding xml.

I think the odsescapechar does not apply in tagsets.excelXP

PeterC

Re: How do I use the ODS escapechar with the excelXP tagset?

Cynthia_sas — Fri, 16 May 2008 12:58:59 GMT

Hi:
I agree with Peter. There's no guarantee, that even if Escapechar did work for changing style, etc, that Escapechar+n would write the same thing that Excel writes for the Alt+Enter (which is what you have to use to put a return into an Excel cell -- outside the world of SAS.)

In fact, when I made a test spreadsheet and saved the file as XML, this is what I got from Excel (in the XML) for the cell:
[pre]
ccc 
ddd
[/pre]

It looks to me like 
 is some kind of named entity (like ) that represents ALT+Enter.

It also looks like Excel is setting the Wrap attribute on the s21 style:
[pre]

[/pre]

But, when I tried to use that string in a cell, even with protectspecialchars=off, Excel did not translate my 
 to a return. Perhaps there is a hex code equivalent of that string which would work better. I frequently find that some things Microsoft does in its own XML I am unable to duplicate.

cynthia

Re: How do I use the ODS escapechar with the excelXP tagset?

deleted_user — Fri, 16 May 2008 14:32:44 GMT

Hi:

Thanks for the help. I got it to work by specifying protectspecialchar=no and using the actual CR value instead of the ods escapechar (replace the "?" with a "&"). What are the consequences of my turning off protectspecialchar? Can I get around any potential issues by just using the htmlencode function?

data test;
x = "aaa?#10;bbb";
run;

proc template;
define style styles.test;
parent=styles.sasweb;
replace Document from Container /
protectspecialchars = off
;
end;
run;

ods _all_ close;
ods tagsets.excelXP file="C:\temp\test.xls" style=styles.sasweb style=styles.test options (sheet_name= "test");

proc print data=WORK.test noobs;
var x;
run;

ods _all_ close;
ods listing;

Re: How do I use the ODS escapechar with the excelXP tagset?

Cynthia_sas — Sat, 17 May 2008 15:59:44 GMT

Jack:
For what you're doing, I suspect there are no real consequences to using protectspecialchars=off. Generally, when protectspecialchars=on, a character like > used in a TITLE:
[pre]title 'Sales > 500';[/pre]

would be "protected" by being translated to > Of course, only you know your data and how important it is to have a CR in your data. It's a tradeoff. And benchmarking or thoroughly reviewing your output is the only method that can reveal the consequences of protectspecialchars=off.

cynthia