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

[Atennissa]

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,

[SuzanneDorinski]

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

[Atennissa]

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,

[SuzanneDorinski]

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

[Atennissa]

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,

[SuzanneDorinski · Accepted solution]

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

[Atennissa]

Thanks a lot, it's perfect !