topic how to merge vertically cells in proc report (xls) ? in ODS and Base Reporting

how to merge vertically cells in proc report (xls) ?

Atennissa — Mon, 09 Oct 2017 20:34:10 GMT

Hi,

how can I merge vertically cells in proc report ? (see example for column unit price).

I have try with spanrows but it doesn't work (it seems to work only for first column but it isn't in my case).

Any idea ?

Thanks,

Re: how to merge vertically cells in proc report (xls) ?

SuzanneDorinski — Tue, 10 Oct 2017 05:00:06 GMT

SPANROWS works with GROUP or ORDER columns. Below is code and output using the SASHELP.CLASS data set.

proc report data=sashelp.class nowd spanrows;
  columns name age sex height weight;
  define sex / group style(column)={vjust=c just=c};
  title j=l 'SPANROWS option works on column that uses GROUP or ORDER';
run;

SPANROWS in PROC REPORT works with GROUP or ORDER columns

Re: how to merge vertically cells in proc report (xls) ?

Atennissa — Tue, 10 Oct 2017 15:29:24 GMT

My table has an "across" statement and it seems to create a problem with spanrows. Here is an example of my SAS code with sashelp.class.

data test2;
set sashelp.class;
id_num = "Information";
run;

ods excel file="&export" options(sheet_name="test");
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold vjust=m}~SAS Community";
ods text="~S={font_size=14pt font_weight=bold vjust=m}";
proc report data=test2 nowd ;
   column ('Measures' weight height) id_num, (name age sex);
   define weight / display ;
   define height / display ;
   define id_num / across '' ;
   define name / display ;
   define age / display ;
   define sex / display;
   run;

ods excel close;

Is there another way to have headers "Measures" and "Information" and also to have sex in merged cells like in your example ?

Thanks,

Re: how to merge vertically cells in proc report (xls) ?

SuzanneDorinski — Tue, 10 Oct 2017 16:38:55 GMT

ods excel file="&export" options(sheet_name="test");
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold vjust=m}~SAS Community";
ods text="~S={font_size=14pt font_weight=bold vjust=m}";

proc report data=sashelp.class nowd spanrows ;
     column ('Measures' weight height)  ('Information' name age sex);
     define sex / order style(column)={vjust=c just=c};
run;

ods excel close;

PROC REPORT using SPANROWS with order variable and header

Re: how to merge vertically cells in proc report (xls) ?

Atennissa — Tue, 10 Oct 2017 20:11:48 GMT

Thanks a lot Suzanne for this answer.

I have another constraint in my table that I didn't mention and which explains the way I've decided to code that table. I need different styles for headers "Measures" and Information". That's why in my SAS code I can specify a general header style in proc report which is applied to "Measures" and another header style in the define line for "Informations". How can I do that in your example ?

Thanks,

Re: how to merge vertically cells in proc report (xls) ?

SuzanneDorinski — Tue, 10 Oct 2017 20:30:32 GMT

In the code below, I applied different colors to the "Measures" and "Information" headers.

I figured out how to do this based on Cynthia Zender's reply (method 2) on this thread: https://communities.sas.com/t5/ODS-and-Base-Reporting/PROC-REPORT-STYLE-IN-HEADER/td-p/91381

ods excel file="&export" options(sheet_name="test");
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold vjust=m}~SAS Community";
ods text="~S={font_size=14pt font_weight=bold vjust=m}";

proc report data=sashelp.class nowd spanrows ;
     column ('~S={foreground=purple}Measures' weight height)  
            ('~S={foreground=green}Information' name age sex);
     define sex / order style(column)={vjust=c just=c};
run;

ods excel close;

PROC REPORT using SPANROWS with headers with different styles

Re: how to merge vertically cells in proc report (xls) ?

Atennissa — Wed, 11 Oct 2017 17:44:48 GMT

Thanks a lot, it's perfect !