ODS Excel destination. Proc report headers ignoring split characters after using ^{sub xxx}

[BruceKayton]

A simplified version of my code is as follows:

/* Escapechar is ^ */

ods excel options (sheet_name="&table" sheet_interval="none" embedded_titles='yes');
title1 "&title";
proc report data=pk3 nowd missing split='@';
col subjectid ('Nominal time post-dose (h)' tp0 'tp0.5'n tp1 tp2 tp3 tp4 tp5 tp8) result_min result_max t_max auc_sum;
define subjectid / 'Subject ID' order;
define result_min / "C^{sub min}@(ng/mL)";
define result_max / "C^{sub max}@(ng/mL)";
define t_max / "t^{sub max}@(h)";
define auc_sum / "AUC^{sub 0-8}@(h&dot.ng/mL)";
run;

The header in the output shows:

Subject ID

0

0.5

1

2

3

4

5

8

Cmin(ng/mL)

Cmax(ng/mL)

tmax(h)

AUC0-8(h▪ng/mL)

Split character is being ignored. I have seen in the post below that this is a known issue. 

https://communities.sas.com/t5/ODS-and-Base-Reporting/Line-not-wrapping-in-ODS-Excel/m-p/324326/high...

Is there a work around? I've tried ^{newline}, inserting '0A'x to no avail. Is there RAW code as in RTF that I could insert for Excel to render?

Thanks in advance

Bruce Kayton 

[Reeza]

Since this is one of the newer features, I suspect your SAS version will be important. What version of SAS are you using? You can find out by using the following code, the output is in the log.

proc product_status;run;

[BruceKayton]

Version is 9.4_M3, but that is irrelevant. If I use the RTF destination it works just fine, output is required in Excel. ExcelXP tagset works too, but I lose some other features with that. Definitely an ODS Excel destination issue.

[Reeza]

ODS EXCEL was implemented only as production in 9.4M3 and it has some bugs still in that version. 9.4M5, is more stable. This type of behaviour can also be destination dependent because different file types use a different method to specify a new line or subscript, so there are definitely variations in the output across different destination types.

If you can provide some sample data I can test your code on SAS 9.4 TSM5 to see if it's fixed or not.

[BruceKayton]

Appears it is fixed after SAS 9.4M3. 

[ballardw]

Will the values under the column work if you specify a width for the column heading that forces a wrap (may want FLOW in the define). You might need one or two spaces instead of your split character.

Perhaps instead of the SUB you might be able to create similar text with Unicode characters.

[BruceKayton]

Tried those. none of those work. FLOW really only applies to the column content, not the header.

[SuzanneDorinski · Accepted solution]

I'm using SAS University Edition, running SAS 9.4M6.  

The split character works, but you need to make sure there's a space before it.  See the example code below.

ods escapechar='^';

%let dot=^{unicode 2022};

ods excel file="/folders/myfolders/ODS Excel examples/split_characters_PROC_REPORT.xlsx";

* need to have space before split character below.  does not work as expected
* if you do NOT include the space! ;

proc report data=sashelp.class nowd missing split='@';
  col name age height sex weight;
  define name / 'Subject@ID' order;
  define age / "C^{sub min} @(ng/ml)";
  define height / "C^{sub max} @(ng/ml)";
  define sex / "t^{sub max} @(ng/ml)";
  define weight / "AUC^{sub 0-8} @(h&dot.ng/ml)";
run;

ods excel close;

The output:

Need a blank before the split character in PROC REPORT with ODS Excel destination if also using other techniques

Edited to add:  ODS TAGSETS.EXCELXP and ODS EXCEL SHOWDOWN was the first result I got when searching on "sas ods excel destination split character".  The examples in that paper show the split character with regular text.  

[BruceKayton]

Must be version related then, because that still doesn't work for me. I'm running SAS 9.4M3

[BruceKayton]

Thanks everyone for the feedback.

Bruce