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

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

9 REPLIES 9
Ksharp
Super User

vjust=center  ?

JoannaL
Fluorite | Level 6

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

Ksharp
Super User

Then post some data to let us test it .

JoannaL
Fluorite | Level 6

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. 

Ksharp
Super User

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;
 
JoannaL
Fluorite | Level 6

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

Ksharp
Super User

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;
JoannaL
Fluorite | Level 6

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?

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 9 replies
  • 8063 views
  • 3 likes
  • 2 in conversation