How to apply font color and add hard returns to a text string and have them display in Excel

[CarolV]

I'm using SAS version 9.4. Some of the variables I pass to Excel contain contain text strings with both font color and hard returns embedded within them. Those  variables display perfectly in the Result window with the correct color coding and hard returns. However, in Excel, the color is displayed but not the hard returns. Oddly, if I create a variable with just hard returns, they are displayed perfectly in Excel as well.

Here is what I’m working with:

DATASET = Test

• Var1 = “X^n^nHeart failure (2014-02-13)^n^nRespiratory failure (2014-02-13)”;

• Var2 = “X [REVISION NEEDED]^n^n^S={foreground=BLUE} Heart failure (2014-02-13) [DELETED]^S={} ^n^nRespiratory failure (2014-02-13)”

SAS Code:

ods Excel file = 'C:\TEST.xlsx';

ods escapechar='^';

proc report data = test nowindows split = "~" headline headskip

  style(report) = {font_face= "arial" just=center font_size = 10pt background=WHITE borderwidth=3 bordercolor=black}

  style(column) = {font_face= "arial" font_size = 10pt background=WHITE borderwidth=3 bordercolor=black}

  style(header) = {font_face= "arial" font_size = 10pt background=mistyrose borderwidth=3 bordercolor=black};

  column var1 var2;

  define  var1/'Var1' width =15  style(header)=Header{background=WHITE} style(column)={vjust=t just=c};

  define  var2/'Var2' width =15 style(header)=Header{background=WHITE} style(column)={vjust=t just=l};

run;

ods Excel close;

ods listing;

PERFECT RESULT WINDOW OUTPUT:

DISAPPOINTING EXCEL FILE (VAR2 is without hard returns because it contains both color formatting in addition to hard returns):

What can I do differently to fix this problem?

THANKS!!!!

[GinaRepole]

I'm not sure why the Excel destination does not allow you to do this properly, but it is definitely a problem with the Excel destination itself. I played around with various code for a while and couldn't figure out a way around it. Are you open to using other file types that may be readable via Excel even if they aren't native? If you change your output file extension to .xls and use the tagsets.excelxp destination, you can create the newlines as you wish.

[Cynthia_sas]

Hi:

  I think there is a note about the newline not working when you also have a style override in the string. The equivalent of the Alt+Enter in Excel is

(pasted in as a picture to avoid having it turn into a line feed in the Forum posting). When I run the code below in 9.4 M5, I get your desired results:

(Note, I did have some issues when using 9.4 M3 and this same code. The issues were gone in 9.4 M5. I did not test with 9.4M4. So, your mileage may vary depending on your version of SAS.)

Here's the code that created the data (again, a screen shot to avoid any posting issues:

and here's the PROC REPORT and ODS EXCEL code:

ods Excel file = 'test_line_break.xlsx' 
          options(flow="tables" row_heights="0,80" embedded_footnotes='yes');
 
ods escapechar='^';
 
proc report data = test nowindows split = "~" headline headskip
  style(report) = {font_face= "arial" just=center font_size = 10pt}
  style(column) = {protectspecialchars=off font_face= "arial" font_size = 10pt}
  style(header) = {font_face= "arial" font_size = 10pt};
  column var1 var2;
  define  var1/'Var1'   
     style(header)=Header{background=WHITE} 
     style(column)={vjust=t just=l};
  define  var2/'Var2'  
     style(header)=Header{background=WHITE} 
     style(column)={vjust=t just=l};
  
    footnote "Run on Version &sysvlong4";
  
run;
  
ods Excel close;

Hope this helps,

cynthia

PS: Here's the Tech Support note. It shows the error with the use of ^{newline} Escapechar function, however the ^n that you were using is the older syntax for that function (so the note still applies):