topic Re: Line not wrapping in ODS Excel in ODS and Base Reporting

Line not wrapping in ODS Excel

FiggyScott — Tue, 10 Jan 2017 16:24:38 GMT

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;

Re: Line not wrapping in ODS Excel

RW9 — Tue, 10 Jan 2017 17:09:36 GMT

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.

Re: Line not wrapping in ODS Excel

rogerjdeangelis — Wed, 11 Jan 2017 01:54:29 GMT

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

Re: Line not wrapping in ODS Excel

FiggyScott — Wed, 11 Jan 2017 19:37:22 GMT

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.

Re: Line not wrapping in ODS Excel

Cynthia_sas — Thu, 12 Jan 2017 12:42:53 GMT

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

Re: Line not wrapping in ODS Excel

Cynthia_sas — Thu, 12 Jan 2017 16:46:07 GMT

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

Re: Line not wrapping in ODS Excel

FiggyScott — Tue, 17 Jan 2017 12:59:58 GMT

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.