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
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!!!!
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):
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.