ods Excel proc report

[Hakob]

I am trying to update the title of the report in Excel to print in one line, but the title is being wrapped into several lines. As you can see in the picture the header of the report is line 1 and 2, and if I am using for example 'width=100%' option for the line, it changes the width of columns as well, but I need to change the width of title (first line) only. I guess I need to separate lines 1 and 2 and have different headers in different lines so for each header I could have separate option. In pic2 "width=100%" has been used in - "style(header)". The title has been set in - "call execute('tit1=compbl("'||catx(' ', type, number, tlf)||'");');" line below.

Code:

*** TLFs ***;
data _null_;
set list ;
by ord1 ord2 ord3 number varnum;

/* If the output is a figure, then run the specific figure macro */
if strip(upcase(type)) = 'FIGURE' and last.number then call execute('%nrstr(%mo_drm_figure(num='||strip(memname)||', grph_n='||_N_||'));');

/* Else if is a table or listing, then do the below */
else do;
if first.number then call execute('ods excel (id=tr1) options (sheet_name="'||compress(memname)||'" autofilter="YES"
frozen_headers="YES" print_header="title" Embedded_Titles="no" sheet_interval="table");'); /*PROTECT_WORKSHEET='ON'*/

if first.number then do;
if strip(type)='Listing' then call execute('proc report data=liblist.'||compress(memname)||' nocenter missing nowindows split="¬" contents="" formchar(2)="_" nowd headline headskip spacing=2');
else if strip(type)='Table' then call execute('proc report data=libtab.'||compress(memname)||' nocenter missing nowindows split="¬" contents="" formchar(2)="_"');
call execute('style(report)=[protectspecialchars=off cellspacing=0 borderwidth=1 frame=box rules=all]');
call execute('style(column)=[font_face="Times New Roman" font_size=10pt font_weight=medium asis=on ]' );
call execute('style(header)=[just=left font_face="Times New Roman" font_size=11pt asis=on font_weight=bold with=100%];');
/* call execute("title j=l h=11pt 'Moderate/Severe COPD Exacerbation Requiring Systemic Corticosteroids or Antibiotics or Hospitalisation or LRTI in the 4 Weeks Prior to Study Entry or During Run-in Period (EC 2)';"); */
call execute('column col:;');
if strip(label) = 'DRM no.' then call execute('format col3 $CHAR6. col4 $CHAR9. ;');
end;

call execute('define '||strip(name)||' / display style(column)={just=left asis=on} flow "'||strip(label)||'";');

if last.number then do;
call execute('compute before _page_ / right style={&tit_style};');
call execute('tit1=compbl("'||catx(' ', type, number, tlf)||'");');
call execute('line tit1 $1000. LS=10.;');
call execute('line "";');
call execute('line "";');
call execute('endcomp;');
call execute('compute after _page_ / left style={&foot_style};');
call execute('line "";');
call execute('line "Program name: '||compress(program)||'.sas" ;');
call execute('line "Run on: '||compress(put(crdate, datetime16.))||'" ;'); /*&textfoot.*/
call execute('line "Extract Date: &extract_date.";');
call execute('line "SAS Version: &sas_ver.";');
call execute('endcomp;');
call execute('run;');
end;
end;
run;

ods excel (id=tr1) close;
ods listing;

%mend;

 pic1pic2

[ger15xxhcker]

In order to have the title of the report print in one line, you will need to alter the code that creates the header. You will need to adjust the declaration of the header style so that the header takes up the full line width, and add a line break after the title:

`call execute('style(header)=[just=left font_face="Times New Roman" font_size=11pt asis=on font_weight=bold with=100%];');
call execute('title j=l h=11pt 'Moderate/Severe COPD Exacerbation Requiring Systemic Corticosteroids or Antibiotics or Hospitalisation or LRTI in the 4 Weeks Prior to Study Entry or During Run-in Period (EC 2)');
call execute('split;');`

[Cynthia_sas]

Hi:

  Also recommend that, in addition to the WIDTH= option, which may or may not work, ODS EXCEL also has 2 suboptions to help you control TITLES and specify width a different way. Here's the documentation site: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsug/p09n5pw9ol0897n1qe04zeur27rv.htm

and here's what to look for on that page:

  You may have tried these already, but I didn't see it in your post (there was a lot to read)  so I thought it worth mentioning.

Cynthia