From: John Hendrickx on
Does anyone know of a way to insert a newline in Excel using the
ExcelXP tagset? I can do it in an RTF file using ^n but that doesn't
work in Excel. I can do it manually in Excel by pressing Alt-Enter,
which apparently inserts a linefeed character '0A'x. But inserting
linefeeds doesn't work. I've tried ^l, <br>, nothing I've tried works.
Any suggestions?

John Hendrickx

Statistician
Clinquest Europe BV, Oss - The Netherlands
www.clinquest.com
From: Mary on
I found out (by mistake!) that adding a slash in a define using Proc Report
will put a return key in the cell, such as

define isv_od_ph_mr/display 'PH/MR OD';

will display the header to the cell as having a PH followed by a newline
followed by MR OD.

-Mary

----- Original Message -----
From: John Hendrickx
To: SAS-L(a)LISTSERV.UGA.EDU
Sent: Tuesday, November 25, 2008 9:59 AM
Subject: Inserting a newline in Excel


Does anyone know of a way to insert a newline in Excel using the
ExcelXP tagset? I can do it in an RTF file using ^n but that doesn't
work in Excel. I can do it manually in Excel by pressing Alt-Enter,
which apparently inserts a linefeed character '0A'x. But inserting
linefeeds doesn't work. I've tried ^l, <br>, nothing I've tried works.
Any suggestions?

John Hendrickx

Statistician
Clinquest Europe BV, Oss - The Netherlands
www.clinquest.com
From: "Terjeson, Mark" on
Hi Mary,

It may or maynot be a slash next time.

You probably found out that it relates to
the SPLIT= option on your proc statement.
e.g.
proc report data=mydata SPLIT='/' ...
or
proc report data=mydata SPLIT='*' ...

for
define isv_od_ph_mr/display 'PH/MR OD';
or
define isv_od_ph_mr/display 'PH*MR OD';
respectively.



Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst
Investment Management & Research
Russell Investments
253-439-2367


Russell
Global Leaders in Multi-Manager Investing





-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of
Mary
Sent: Tuesday, November 25, 2008 8:52 AM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Re: Inserting a newline in Excel

I found out (by mistake!) that adding a slash in a define using Proc
Report
will put a return key in the cell, such as

define isv_od_ph_mr/display 'PH/MR OD';

will display the header to the cell as having a PH followed by a newline
followed by MR OD.

-Mary

----- Original Message -----
From: John Hendrickx
To: SAS-L(a)LISTSERV.UGA.EDU
Sent: Tuesday, November 25, 2008 9:59 AM
Subject: Inserting a newline in Excel


Does anyone know of a way to insert a newline in Excel using the
ExcelXP tagset? I can do it in an RTF file using ^n but that doesn't
work in Excel. I can do it manually in Excel by pressing Alt-Enter,
which apparently inserts a linefeed character '0A'x. But inserting
linefeeds doesn't work. I've tried ^l, <br>, nothing I've tried works.
Any suggestions?

John Hendrickx

Statistician
Clinquest Europe BV, Oss - The Netherlands
www.clinquest.com
From: John Hendrickx on
The split character does work for header rows in ODS destinations but
not in the body of the table. For RTF destinations, you can use ^n to
insert a newline in the body of the table (where "^" is the ods
escapechar). But that doesn't work for ods tagsets.ExcelXP. Neither
does including a linefeed character '0A'x in the body of the text.

A solution is to use a character that doesn't occur elsewhere in the
text, e.g. "\", then replace that with linefeed characters using VBA
in Excel.

Sub Macro1()
Selection.Replace What:="\", Replacement:=Chr(10), LookAt:=xlPart,
_
SearchOrder:=xlByColumns, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Best regards,
John Hendrickx

On 25 nov, 18:09, Mterje...(a)RUSSELL.COM ("Terjeson, Mark") wrote:
> Hi Mary,
>
> It may or maynot be a slash next time.
>
> You probably found out that it relates to
> the SPLIT= option on your proc statement.
> e.g.
>      proc report data=mydata SPLIT='/' ...
> or
>      proc report data=mydata SPLIT='*' ...
>
> for
>      define isv_od_ph_mr/display 'PH/MR OD';
> or
>      define isv_od_ph_mr/display 'PH*MR OD';
> respectively.
>
> Hope this is helpful.
>
> Mark Terjeson
> Senior Programmer Analyst
> Investment Management & Research
> Russell Investments
> 253-439-2367
>
> Russell
> Global Leaders in Multi-Manager Investing
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SA...(a)LISTSERV.UGA.EDU] On Behalf Of
>
> Mary
> Sent: Tuesday, November 25, 2008 8:52 AM
> To: SA...(a)LISTSERV.UGA.EDU
> Subject: Re: Inserting a newline in Excel
>
> I found out (by mistake!) that adding a slash in a define using Proc
> Report
> will put a return key in the cell, such as
>
> define isv_od_ph_mr/display 'PH/MR OD';
>
> will display the header to the cell as having a PH followed by a newline
> followed by MR OD.
>
> -Mary
>
> ----- Original Message -----
> From: John Hendrickx
> To: SA...(a)LISTSERV.UGA.EDU
> Sent: Tuesday, November 25, 2008 9:59 AM
> Subject: Inserting a newline in Excel
>
> Does anyone know of a way to insert a newline in Excel using the
> ExcelXP tagset? I can do it in an RTF file using ^n but that doesn't
> work in Excel. I can do it manually in Excel by pressing Alt-Enter,
> which apparently inserts a linefeed character '0A'x. But inserting
> linefeeds doesn't work. I've tried ^l, <br>, nothing I've tried works.
> Any suggestions?
>
> John Hendrickx
>
> Statistician
> Clinquest Europe BV, Oss - The Netherlandswww.clinquest.com

From: Don Henderson on
A birdie comments:

data test;
string='This is&#10;a test';
run;

ods path work.tmplmst(update) sashelp.tmplmst(read);

filename tagset url
'http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl';

proc template;
define style styles.XLDefault;
parent=styles.Default;
style data_unprotect from data /
protectspecialchars=off;
end;
run; quit;

ods listing close;
ods tagsets.excelxp path='c:\temp' file='text.xml' style=XLDefault
options(autofit_height='yes');
proc print data=test;
var string / style(data)=data_unprotect;
run; quit;
ods tagsets.excelxp close;

Regards,
Donh

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of John
Hendrickx
Sent: Wednesday, November 26, 2008 3:49 AM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Re: Inserting a newline in Excel

The split character does work for header rows in ODS destinations but not in
the body of the table. For RTF destinations, you can use ^n to insert a
newline in the body of the table (where "^" is the ods escapechar). But that
doesn't work for ods tagsets.ExcelXP. Neither does including a linefeed
character '0A'x in the body of the text.

A solution is to use a character that doesn't occur elsewhere in the text,
e.g. "\", then replace that with linefeed characters using VBA in Excel.

Sub Macro1()
Selection.Replace What:="\", Replacement:=Chr(10), LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Best regards,
John Hendrickx

On 25 nov, 18:09, Mterje...(a)RUSSELL.COM ("Terjeson, Mark") wrote:
> Hi Mary,
>
> It may or maynot be a slash next time.
>
> You probably found out that it relates to the SPLIT= option on your
> proc statement.
> e.g.
> proc report data=mydata SPLIT='/' ...
> or
> proc report data=mydata SPLIT='*' ...
>
> for
> define isv_od_ph_mr/display 'PH/MR OD'; or
> define isv_od_ph_mr/display 'PH*MR OD'; respectively.
>
> Hope this is helpful.
>
> Mark Terjeson
> Senior Programmer Analyst
> Investment Management & Research
> Russell Investments
> 253-439-2367
>
> Russell
> Global Leaders in Multi-Manager Investing
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SA...(a)LISTSERV.UGA.EDU] On Behalf Of
>
> Mary
> Sent: Tuesday, November 25, 2008 8:52 AM
> To: SA...(a)LISTSERV.UGA.EDU
> Subject: Re: Inserting a newline in Excel
>
> I found out (by mistake!) that adding a slash in a define using Proc
> Report will put a return key in the cell, such as
>
> define isv_od_ph_mr/display 'PH/MR OD';
>
> will display the header to the cell as having a PH followed by a
> newline followed by MR OD.
>
> -Mary
>
> ----- Original Message -----
> From: John Hendrickx
> To: SA...(a)LISTSERV.UGA.EDU
> Sent: Tuesday, November 25, 2008 9:59 AM
> Subject: Inserting a newline in Excel
>
> Does anyone know of a way to insert a newline in Excel using the
> ExcelXP tagset? I can do it in an RTF file using ^n but that doesn't
> work in Excel. I can do it manually in Excel by pressing Alt-Enter,
> which apparently inserts a linefeed character '0A'x. But inserting
> linefeeds doesn't work. I've tried ^l, <br>, nothing I've tried works.
> Any suggestions?
>
> John Hendrickx
>
> Statistician
> Clinquest Europe BV, Oss - The Netherlandswww.clinquest.com