The SAS Output Delivery System and reporting techniques

Line not wrapping in ODS Excel

Reply
New Contributor
Posts: 3

Line not wrapping in ODS Excel

  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;
Super User
Super User
Posts: 7,942

Re: Line not wrapping in ODS Excel

Posted in reply to FiggyScott

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.

Valued Guide
Posts: 505

Re: Line not wrapping in ODS Excel

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}";

New Contributor
Posts: 3

Re: Line not wrapping in ODS Excel

[ Edited ]
Posted in reply to rogerjdeangelis

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.

SAS Super FREQ
Posts: 8,862

Re: Line not wrapping in ODS Excel

Posted in reply to FiggyScott

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

 

newline_split.png

SAS Super FREQ
Posts: 8,862

Re: Line not wrapping in ODS Excel

[ Edited ]
Posted in reply to Cynthia_sas

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
super_split.png

New Contributor
Posts: 3

Re: Line not wrapping in ODS Excel

Posted in reply to Cynthia_sas

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.

Ask a Question
Discussion stats
  • 6 replies
  • 557 views
  • 2 likes
  • 4 in conversation