I am trying to create an Excel file using Proc Report and ODS Excel with a column heading that has both a line break and a superscript (column VALUE2). The issue is the line break is being ignored in Excel, but it works properly in HTML (SAS Output window). My output needs to be an Excel file because it eventually is being converted to a PDF (and the specialized process needs Excel as input).
Using two line breaks works fine
Using two superscript notations works fine
Below is a stripped down/simplified version of the program I am trying to run. I have tried running this in SAS 9.2 and 9.4 with similar results. Any help on how to make both notations work together would be greatly appreciated.
ata mytable;
realrow = '01';
value1 = 34;
value2 = 19;
output;
realrow = '01';
value1 = 12;
value2 = 55;
output;
run;
proc format;
value $ tab12fmt
'01' = 'Total, all items';
run;
ods excel file="H:\mytab.xlsx";
options missing='' nodate;
ods escapechar='~';
title "My Table 1";
proc report data=mytable missing;
label value1 = 'Amount~n(thousands of~ndollars)'
value2 = "Average per~nangler(dollars)~{super 2}";
column realrow value1 value2;
define realrow / format=$tab12fmt. ;
define value1 / style(header)=[just=r]
style(column)=[borderrightcolor=white
borderrightwidth=0];
define value2 / style(header)=[just=r]
style(column)=[borderleftcolor=white
borderleftwidth=0 borderrightcolor=white
borderrightwidth=0];
compute realrow;
call define(_row_,'style','style=[bordertopcolor=white bordertopwidth=0
borderbottomcolor=white borderbottomwidth=0
font_weight=bold]');
endcomp;
run;
ods excel close;
Why not go directly to PDF (or to RTF then PDF)? It will make your life far simpler in the long run. As for line splits and such like, its a real pain, Excel doesn't have a newline option (~n), you would need to insert the ASCII character which represents the Alt + Enter you get if typing directly in Excel. As noted though, Excel isn't really a good tool for reports.
These worked for me
Using a split character
proc report data=mytable missing split='#';
label value1 = 'Amount#(thousands of~ndollars)'
value2 = "Average per#angler(dollars)~{super 2}";
OR
proc report data=mytable missing;
label value1 = 'Amount~{newline}(thousands of~ndollars)'
value2 = "Average per~{newline}angler(dollars)~{super 2}";
RW9 - As I mentioned, where I work there is a specialized procedure/process that converts Excel into PDFs, adding a variety of additional formatting codes I don't want to have to deal with inserting myself.
Roger - Neither of those solutions work for me (and I believe ~{newline} is the same thing as ~n). At this point I have to assume the issue is either the versions of the software I am using or a patch (or lack of) that is contributing to my difficulties.
Thank you both for your help though.
Hi:
I guess I'm confused. Both the SPLIT= option in PROC REPORT and the NEWLINE function in ESCAPECHAR work for me. I am opening the Excel output with Excel/Office 2013 and opening the HTML in a browser. In my output, Excel respects both of these techniques (see below)
cynthia
Hi:
I understand the problem better. The split character and newline do work by themselves. But if you have a superscript function WITH a split character or newline, then the header is broken in ODS Excel. My understanding is that this is a defect that is being worked on, so there isn't a "fix" yet. You'd need to work with Tech Support to find out the anticipated date for any fix -- or switch to a different destination, where the split and superscript will work. (screen shot below shows that split is "broken" when superscript is used.
cynthia
Cynthia - Thanks for your reply. I had a feeling the problem was a failure of the ODS Excel coding/functionality. There may be at least one work around, otherwise we will have to revert to using the old process for creating our tables. I had been hoping to speed up the process with coding the entire table myself.
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.