topic Re: Merging Cells Vertically in PROC REPORT RTF in ODS and Base Reporting

Merging Cells Vertically in PROC REPORT RTF

JoannaL — Thu, 21 Apr 2016 02:54:48 GMT

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.

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;

Re: Merging Cells Vertically in PROC REPORT RTF

Ksharp — Thu, 21 Apr 2016 03:49:36 GMT

vjust=center ?

Re: Merging Cells Vertically in PROC REPORT RTF

JoannaL — Thu, 21 Apr 2016 05:22:22 GMT

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

Re: Merging Cells Vertically in PROC REPORT RTF

Ksharp — Thu, 21 Apr 2016 06:22:00 GMT

Then post some data to let us test it .

Re: Merging Cells Vertically in PROC REPORT RTF

JoannaL — Thu, 21 Apr 2016 06:48:42 GMT

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.

Re: Merging Cells Vertically in PROC REPORT RTF

Ksharp — Thu, 21 Apr 2016 07:48:28 GMT

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;

Re: Merging Cells Vertically in PROC REPORT RTF

JoannaL — Thu, 21 Apr 2016 09:04:41 GMT

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;

Re: Merging Cells Vertically in PROC REPORT RTF

Ksharp — Thu, 21 Apr 2016 09:19:56 GMT

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;

Re: Merging Cells Vertically in PROC REPORT RTF

JoannaL — Thu, 21 Apr 2016 09:23:08 GMT

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?

Re: Merging Cells Vertically in PROC REPORT RTF

Ksharp — Thu, 21 Apr 2016 09:32:51 GMT

"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.