BookmarkSubscribeRSS Feed
FiggyScott
Calcite | Level 5

  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;
6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

rogerjdeangelis
Barite | Level 11
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}";

FiggyScott
Calcite | Level 5

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.

Cynthia_sas
SAS Super FREQ

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

Cynthia_sas
SAS Super FREQ

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

FiggyScott
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3838 views
  • 2 likes
  • 4 in conversation