Inserting a newline in Excel
in [SAS]
|
From: John Hendrickx on 27 Nov 2008 03:20 Hello Don, I'm afraid I can't get the birdie's solution to work. I'm using version 1.86 of the Excel XP tagset in SAS 9.1.3. The program below prints variable string as a single line. Here are some other solutions I've tried with no success: data test; string='This is a test'; run; data test2; string=cat('This is',byte(10),'test 2'); run; ods escapechar='^'; /* Can't use ^n, excel file will be unopenable */ data test3; string=cat('This is ^l test 3'); run; /* Can't use <br>, excel file will be unopenable */ data test4; string=cat('This is <br> test 4'); run; proc template; define style styles.XLDefault; parent=styles.Default; style data_unprotect from data / protectspecialchars=off; end; run; quit; ods listing close; /* Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08) */ ods tagsets.excelxp file='.\text.xml' style=XLDefault options(autofit_height='yes' sheet_name='test') ; proc print data=test; var string / style(data)=data_unprotect; run; ods tagsets.excelxp options(sheet_name='test2'); proc print data=test2; var string / style(data)=data_unprotect; run; ods tagsets.excelxp options(sheet_name='test3'); proc print data=test3; var string / style(data)=data_unprotect; run; ods tagsets.excelxp close; On 26 nov, 17:33, donaldjhender...(a)HOTMAIL.COM (Don Henderson) wrote: > A birdie comments: > > data test; > string='This is > 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:SA...(a)LISTSERV.UGA.EDU] On Behalf Of John > > Hendrickx > Sent: Wednesday, November 26, 2008 3:49 AM > To: SA...(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
From: John Hendrickx on 28 Nov 2008 04:35
Ok, I found a solution. The trick is to insert "#10;" in the text, then a newline will be inserted, providing "protectspecialchars=off" as Don's birdie suggested. In general, if you want to find out how to do something using the ExcelXP tagset, you can modify the Excel file created using the tagset, open it in a text editor (it's an XML file) and see how your modification changed the XML code. In this case, I inserted alt-enter at the point I wanted the new line and saw that this cause a "#10;" to be inserted. A birdie told met this once, but I forgot :-(. Good luck, John Hendrickx data test; string='This is a test'; run; proc template; define style styles.XLDefault; parent=styles.Default; style data_unprotect from data / protectspecialchars=off; end; run; quit; ods listing close; /* Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08) */ ods tagsets.excelxp file='.\text2.xml' style=XLDefault options(autofit_height='yes' sheet_name='test') ; proc print data=test; var string / style(data)=data_unprotect; run; ods tagsets.excelxp close; On 27 nov, 09:20, John Hendrickx <John2.Hendri...(a)gmail.com> wrote: > Hello Don, > > I'm afraid I can't get the birdie's solution to work. I'm using > version 1.86 of the Excel XP tagset in SAS 9.1.3. The program below > prints variable string as a single line. Here are some other solutions > I've tried with no success: > > data test; > string='This is > a test'; > run; > > data test2; > string=cat('This is',byte(10),'test 2'); > run; > ods escapechar='^'; > /* > Can't use ^n, excel file will be unopenable > */ > data test3; > string=cat('This is ^l test 3'); > run; > /* > Can't use <br>, excel file will be unopenable > */ > data test4; > string=cat('This is <br> test 4'); > run; > > proc template; > define style styles.XLDefault; > parent=styles.Default; > style data_unprotect from data / > protectspecialchars=off; > end; > run; quit; > > ods listing close; > /* Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08) */ > ods tagsets.excelxp file='.\text.xml' style=XLDefault > options(autofit_height='yes' sheet_name='test') ; > > proc print data=test; > var string / style(data)=data_unprotect; > run; > > ods tagsets.excelxp options(sheet_name='test2'); > proc print data=test2; > var string / style(data)=data_unprotect; > run; > > ods tagsets.excelxp options(sheet_name='test3'); > proc print data=test3; > var string / style(data)=data_unprotect; > run; > > ods tagsets.excelxp close; > > On 26 nov, 17:33, donaldjhender...(a)HOTMAIL.COM (Don Henderson) wrote: > > > A birdie comments: > > > data test; > > string='This is > > 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:SA...(a)LISTSERV.UGA.EDU] On Behalf Of John > > > Hendrickx > > Sent: Wednesday, November 26, 2008 3:49 AM > > To: SA...(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 |