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,
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;
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;
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,
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;
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,
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;
Thanks a lot, it's perfect !
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.