BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
4 REPLIES 4
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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;
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1273 views
  • 0 likes
  • 2 in conversation