topic ODS Excel destination. Proc report headers ignoring split characters after using ^{sub xxx} in ODS and Base Reporting

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

BruceKayton — Wed, 09 Jan 2019 22:08:00 GMT

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.5

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/highlight/true#M17722

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

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

Reeza — Wed, 09 Jan 2019 21:20:42 GMT

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;

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

ballardw — Wed, 09 Jan 2019 21:42:21 GMT

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.

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

BruceKayton — Wed, 09 Jan 2019 22:37:03 GMT

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.

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

Reeza — Wed, 09 Jan 2019 22:52:07 GMT

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.

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

BruceKayton — Thu, 10 Jan 2019 17:34:33 GMT

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

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

SuzanneDorinski — Thu, 10 Jan 2019 22:43:13 GMT

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.

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

BruceKayton — Fri, 11 Jan 2019 01:37:19 GMT

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

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

BruceKayton — Fri, 11 Jan 2019 01:39:29 GMT

Appears it is fixed after SAS 9.4M3.

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

BruceKayton — Fri, 11 Jan 2019 01:40:13 GMT

Thanks everyone for the feedback.

Bruce