The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

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,

 


Accepted Solutions
Solution
‎10-11-2017 01:44 PM
Frequent Contributor
Posts: 105

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

Posted in reply to Atennissa

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.jpgPROC REPORT using SPANROWS with headers with different styles

 

View solution in original post


All Replies
Frequent Contributor
Posts: 105

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

Posted in reply to Atennissa

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 on GROUP or ORDER column.jpgSPANROWS in PROC REPORT works with GROUP or ORDER columns

Occasional Contributor
Posts: 5

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

Posted in reply to SuzanneDorinski

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,

Frequent Contributor
Posts: 105

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

Posted in reply to Atennissa
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 headers.jpgPROC REPORT using SPANROWS with order variable and header

 

Occasional Contributor
Posts: 5

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

Posted in reply to SuzanneDorinski

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,

Solution
‎10-11-2017 01:44 PM
Frequent Contributor
Posts: 105

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

Posted in reply to Atennissa

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.jpgPROC REPORT using SPANROWS with headers with different styles

 

Occasional Contributor
Posts: 5

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

Posted in reply to SuzanneDorinski

Thanks a lot, it's perfect !

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 488 views
  • 0 likes
  • 2 in conversation