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.
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
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:
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.
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.
Appears it is fixed after SAS 9.4M3.
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.
Tried those. none of those work. FLOW really only applies to the column content, not the header.
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:
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.
Must be version related then, because that still doesn't work for me. I'm running SAS 9.4M3
Thanks everyone for the feedback.
Bruce
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.