I need to create an excel-file with embedded titles and the title needs text aligned on left and right in the same line. This can easily be done with a title-statement (see code below), but as soon as ods excel comes into play, the automatic column-spanning hides parts of the left-aligned title. So i moved the title-text to the data step, with rwi inserting rows before the header of a table works well, except for one thing: the heights of the rows is unnecessary large.
I can't use the option row_heights in the ods-statement, because i need the automatic height-calculation for the data.
ods excel
file= "rwi_title.xlsx"
options(
flow= "data"
sheet_interval= "none"
embedded_titles= 'yes'
);
proc sql noprint;
select Name into :varList separated by ' '
from sashelp.vcolumn
where libname = 'SASHELP' and memname = 'CARS'
;
quit;
/*title1 justify=left "The most fantastic sashelp.cars" justify=right "%sysfunc(today(), ddmmyyp10.)";*/
data _null_;
set sashelp.cars end=done;
if _n_ = 1 then do;
declare odsout rwi();
rwi.table_start();
rwi.head_start();
rwi.row_start(); /* height seems to be calculated without considering column_span */
rwi.format_cell(data: 'The most fantastic sashelp.cars', column_span: 10, style_attr: 'color= red');
rwi.format_cell(data: "%sysfunc(today(), ddmmyyp10.)", column_span: 5, just: 'R', style_attr: 'color= red');
rwi.row_end();
rwi.row_start();
rwi.format_cell(data: 'CARS', column_span: 10, style_attr: 'color= red');
rwi.format_cell(data: 'CARS', column_span: 5, just: 'R', style_attr: 'color= red');
rwi.row_end();
rwi.row_start();
do i = 1 to countw("&varList");
rwi.format_cell(data: scan("&varList.", i));
end;
rwi.row_end();
rwi.head_end();
rwi.body_start();
end;
rwi.row_start();
do i = 1 to countw("&varList");
rwi.format_cell(data: vvaluex(scan("&varList.", i)));
end;
rwi.row_end();
if done then do;
rwi.body_end();
rwi.table_end();
end;
run;
title;
ods excel close;
Is this is feature in 9.4m5?
Hi:
Removing RWI from the equation, with just a plain title statement, I can use the TITLE_FOOTNOTE_WIDTH= sub-option to specify the number of columns that the title should span. This works for me:
Of course the LINE statement in PROC REPORT automatically spans the whole table. But the sub-option and the title statement should work no matter what the procedure or step is.
Cynthia
Thanks for your time, will look try it next week, when i am back in office.
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.