The SAS Output Delivery System and reporting techniques

Merging Cells Vertically in PROC REPORT RTF

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Merging Cells Vertically in PROC REPORT RTF

Hi, How can I merge cells vertically for the first column (i.e. Age, Sex, Height, Weight, BMI) each with different number of rows in proc report? I want the output to look something like the attached photo. I've tried using style(column)=[vjust=middle just=left] but it doesn't work. My codes are below.

Capture.PNG

 

 

ods rtf file="C:\User\Desktop\test.rtf";
proc report data=all nowd spanrows

colwidth=10
spacing=5
headline headskip split='^'

style(report)=[cellspacing=2 borderwidth=1 bordercolor=black]
style(header)=[color=blue borderwidth=1 bordercolor=black fontfamily=calibri fontsize=2]
style(column)=[color=black borderwidth=1 bordercolor=black fontfamily=calibri fontsize=1];

column subgroup table pos order variable measure a b c d;

define subgroup / order order=internal noprint;
define table / order order=internal noprint;
define pos / order order=internal noprint;
define order / order order=internal noprint;
define variable / order order=internal display style(column)=[vjust=middle just=left];
define measure / display "Measure" left;
define a / display center;
define b /  display center;
define c /  display center;
define d / display center;
break after subgroup / page;
compute before _page_ / left;
line subgroup subgroup.;
endcomp;
run;

ods rtf close;

 

 


Accepted Solutions
Solution
‎04-21-2016 05:23 AM
Grand Advisor
Posts: 9,596

Re: Merging Cells Vertically in PROC REPORT RTF

You have missing value in your group variables.

And don't define DISPLAY usage for group variable VARIABLE, since it is GROUP usage.

 

proc import datafile='/folders/myfolders/test.xlsx' dbms=xlsx out=have replace;
run;
data all;
 set have;
 if not missing(test) then _test=test;
  else test=_test;
 if not missing(variable) then _variable=variable;
  else variable=_variable; 
 retain _test _variable;
run;

ods rtf file="/folders/myfolders/test.rtf";
proc report data=all nowd  split='^' spanrows;
column table pos variable order measure a b c d;
define table/ group noprint;
define pos/ group noprint;
define variable / group  style(column)=[vjust=middle just=left];
define order/order noprint;

define measure / display "Measure" left;
define a / display center;
define b /  display center;

compute before _page_ / left;
line 'xxxxxxxxxxxxxx';
endcomp;
run;
ods rtf close;
 

View solution in original post


All Replies
Grand Advisor
Posts: 9,596

Re: Merging Cells Vertically in PROC REPORT RTF

vjust=center  ?

Occasional Contributor
Posts: 10

Re: Merging Cells Vertically in PROC REPORT RTF

It just doesn't work. I still get rows coming out unmerged. 

Grand Advisor
Posts: 9,596

Re: Merging Cells Vertically in PROC REPORT RTF

Then post some data to let us test it .

Occasional Contributor
Posts: 10

Re: Merging Cells Vertically in PROC REPORT RTF

I have uploaded the data. There are two variable names (variable & varname) which I was testing. vjust(center/middle) doesn't work with either of them. 

 

Thanks. 

Solution
‎04-21-2016 05:23 AM
Grand Advisor
Posts: 9,596

Re: Merging Cells Vertically in PROC REPORT RTF

You have missing value in your group variables.

And don't define DISPLAY usage for group variable VARIABLE, since it is GROUP usage.

 

proc import datafile='/folders/myfolders/test.xlsx' dbms=xlsx out=have replace;
run;
data all;
 set have;
 if not missing(test) then _test=test;
  else test=_test;
 if not missing(variable) then _variable=variable;
  else variable=_variable; 
 retain _test _variable;
run;

ods rtf file="/folders/myfolders/test.rtf";
proc report data=all nowd  split='^' spanrows;
column table pos variable order measure a b c d;
define table/ group noprint;
define pos/ group noprint;
define variable / group  style(column)=[vjust=middle just=left];
define order/order noprint;

define measure / display "Measure" left;
define a / display center;
define b /  display center;

compute before _page_ / left;
line 'xxxxxxxxxxxxxx';
endcomp;
run;
ods rtf close;
 
Occasional Contributor
Posts: 10

Re: Merging Cells Vertically in PROC REPORT RTF

Thanks, KeShan. That works. However, I have another problem. I tried adding another column (pvalue) to the report. But it does not merge as well. I have run the code to retain the pvalues as shown in your example. 

 

proc report data=t2 nowd split='^' spanrows;

column table pos variable order measure a b pvalue;
define table/ group noprint;
define pos/ group noprint;
define variable / group style(column)=[vjust=middle just=left];

define order/order noprint;

define measure / display "Measure" left;
define a / display center;
define b / display center;
define pvalue / group style(column)=[vjust=middle just=center];

break after table/page;
compute before _page_ / left;

 


line table table.;
endcomp;
run;

 

Capture.PNG

Grand Advisor
Posts: 9,596

Re: Merging Cells Vertically in PROC REPORT RTF

Ah.Here is.

 

proc import datafile='/folders/myfolders/test.xlsx' dbms=xlsx out=have replace;
run;
data all;
 set have;
 if not missing(test) then _test=test;
  else test=_test;
 if not missing(variable) then _variable=variable;
  else variable=_variable;
 if not missing(pvalue) then _pvalue=pvalue;
  else pvalue=_pvalue; 
 retain _test _variable _pvalue;
run;

ods rtf file="/folders/myfolders/test.rtf";
proc report data=all nowd  split='^' spanrows;
column table pos variable  measure a b pvalue order     ;
define table/ group noprint;
define pos/ group noprint;
define variable / group  style(column)=[vjust=middle just=left];
define order/order noprint;
define pvalue/ group  style(column)=[vjust=middle just=left];

define measure / display "Measure" left ;
define a / display center ;
define b /  display center ;


compute before _page_ / left;
line 'xxxxxxxxxxxxxx';
endcomp;
run;
ods rtf close;
Occasional Contributor
Posts: 10

Re: Merging Cells Vertically in PROC REPORT RTF

Thanks so much! So I should conclude that all GROUP variables must be placed before the ORDER variable in the column statement. Else the vertical merge will not work. And there can only be one ORDER variable in a report?

Grand Advisor
Posts: 9,596

Re: Merging Cells Vertically in PROC REPORT RTF

"So I should conclude that all GROUP variables must be placed before the ORDER variable in the column statement. Else the vertical merge will not work."

Yes. I think so.

 

"And there can only be one ORDER variable in a report?"

No. I don't think so. You can take ORDER the same as GROUP. If you replace all the GROUP with ORDER in the code, you will find

that still worked. 

PROC REPORT take variables from left to right. So when it meet ORDER variable(order) ,will take it as a GROUP usage variable,

Therefore when variable pvalue after order, PROC REPORT will take ORDER as the first group variable and PVALUE as the second group variable. That is wrong. So you need put PVALUE before ORDER.

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 1664 views
  • 3 likes
  • 2 in conversation