Row height to large when using ods excel and rwi

andreas_lds — Fri, 08 May 2020 06:34:05 GMT

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?

Re: Row height to large when using ods excel and rwi

Cynthia_sas — Fri, 08 May 2020 22:45:12 GMT

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

Re: Row height to large when using ods excel and rwi

andreas_lds — Sat, 09 May 2020 20:36:38 GMT

Thanks for your time, will look try it next week, when i am back in office.

topic Re: Row height to large when using ods excel and rwi in ODS and Base Reporting

Row height to large when using ods excel and rwi

Re: Row height to large when using ods excel and rwi

Re: Row height to large when using ods excel and rwi