The SAS Output Delivery System and reporting techniques

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

Reply
N/A
Posts: 0

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

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.
N/A
Posts: 0

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

Posted in reply to deleted_user
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
SAS Super FREQ
Posts: 8,868

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

Posted in reply to deleted_user
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
N/A
Posts: 0

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

Posted in reply to deleted_user
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;
SAS Super FREQ
Posts: 8,868

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

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 4 replies
  • 287 views
  • 0 likes
  • 2 in conversation