BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Atennissa
Calcite | Level 5

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
SuzanneDorinski
Lapis Lazuli | Level 10

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

 

View solution in original post

6 REPLIES 6
SuzanneDorinski
Lapis Lazuli | Level 10

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 columnsSPANROWS in PROC REPORT works with GROUP or ORDER columns

Atennissa
Calcite | Level 5

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
Lapis Lazuli | Level 10
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 headerPROC REPORT using SPANROWS with order variable and header

 

Atennissa
Calcite | Level 5

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
Lapis Lazuli | Level 10

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

 

Atennissa
Calcite | Level 5

Thanks a lot, it's perfect !

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 8552 views
  • 1 like
  • 2 in conversation