How to Merge cells horizontally in ODS Excel output.

[SASuserlot]

I am looking to achieve the ods excel in specific format mentioned in the image( shaded area) . My main aim to merge cells horizontally. I am ok to manipulate the data as per the output requirement. (like displaying the 150/70 across the hr and Vit variables in original dataset in order to merge horizontally). I am not looking for any colors at this point. Please advice your inputs.

@Cynthia_sas  Can you please take a look and advice when you have chance.

Thanks everyone.

data dummy;
do round = '1st Round', '2nd Round', '3rd Round';;
do time = "12:30", "13:45","14:15";
do HR = 71 to 76 by 2;
do Vit = 15 to 18 by 1;
do height = 150 to 153 by .5;
do weight = 70 to 77 by 2;
do Orient = 'STANDING', 'SITTING','';
output;
end;
end;
end;
end;
end;
end;
end;
run;

proc sort data = dummy; by round Orient time ;run;

data d1;
set dummy;
by round;
if first.round;
Orient=catx("/",strip(put(HEIGHT,best.)),strip(put(weight,best.)));
Comb = 'Height/Weight';
run;

data d2;
set dummy;
by round Orient time;
if first.time;
run;

data d3;
set d2(drop = height weight) d1(keep =round time Orient Comb);
run;


proc sort data = d3; by round Orient time;run;

data d4;
set d3;
by round Orient time;
if last.Orient;
where not missing(Orient);
if hr ^= . then comb= "Vital";
run;
ods ESCAPECHAR = '^';
ods excel file='C:\temp\New folder\dummy1.xlsx' ;


   PROC REPORT data= d4 nowindows missing spanrows 
					      style(report)=[font=("Times New Roman", 11pt) ]
					      style(header)=[font=("Times New Roman", 11pt, bold) just=center borderbottomwidth=4pt bordercolor=black ]
					      style(column)=[font=("Times New Roman", 11pt)  ] ;
      COLUMN    round comb time  (" "  orient hr vit);
      DEFINE round   /   order  "round" group;* style=[just=l vjust=t cellwidth=5 cm borderbottomwidth=4pt bordercolor=black ];
      DEFINE comb   /  order  "comb" display style=[just=l vjust=t cellwidth=6 cm ];
      DEFINE time    /  "Time" order style=[just=c vjust=t cellwidth=1.5 cm ];
      DEFINE orient   /  order  "orient" display style=[just=c vjust=t cellwidth=4 cm ];
      DEFINE hr   /"hr" display style=[just=c vjust=t cellwidth=2cm ];
      DEFINE vit    /"vit" display style=[just=c vjust=t cellwidth=2cm ];
	  *compute before _page_/ style=[font=("Times New Roman", 11pt, bold ) background=white ];
		*line usubjid $varying80. ;
	  *endcomp;
	  run;
ods excel close;

[Cynthia_sas]

Hi:
Inside a PROC REPORT report, you can have spanning column headers like this at the top of the report :

and you can have spanning row header areas for group or order items such as you achieve when you use SPANROWS (which you are doing in your code)

But, you cannot force one cell on a row to merge with other cells on the same row horizontally as you envision in your screen shot (it was sort of fuzzy for me because it was very small, but it looked like you wanted your percent value for ORIENT column to span 3 columns: ORIENT, HR and VIT? Excel has a "merge" cell capability for either rows or columns in a spreadsheet. PROC REPORT does not have this capability.

Cynthia

[SASuserlot]

Thank you for your prompt response @Cynthia_sas . I am trying to attach the bigger image here. And your are right. I am trying to merge orient , hr, and Vit Cells. Like you mentioned I can not use rowspans, because these are in the middle of the sheet not as header. I read one of your responses using the sashelp. iris data, where you have the cells merged horizontally. In that response cells merged because the its the summary stat calculation ( avg). do you have any other suggestions how I can present it that gives better output . Any kind of suggestion are welcomed.

your previous response on cell merge link:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Selective-Horizontal-Cell-Merging-in-ExcelXP/t...

[SASuserlot]

Just ask about idea regarding meging cells horizontally, Is it possible to  remove the  borders for the specific cells mentioned so that its looks like one cell . It don't need to be center the word in Cell.

[Cynthia_sas]

Hi,
Disappearing the interior table lines might work in HTML, RTF or PDF, but Excel can be very touchy about interior border lines. You might put all the work into fiddling with the border style attributes only to find that it doesn't work for ODS EXCEL.
You might be able to achieve the cell merging you want with the Report Writing interface (RWI) in HTML or PDF. But the RWI is not respected by all ODS Destinations. I have not tried the Complex Reports paper sent to ODS Excel because it was not production when I wrote that paper.

  
However, this 2020 paper https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2020/4243-2020.pdf illustrates using the RWI with ODS EXCEL, so it may be possible to do column spanning, although it is not shown in this paper, it might be useful for you to look at the code in his paper and to revisit the code in my paper. Learning the RWI syntax is not a trivial task and you'd have to convert your PROC REPORT step to an RWI step.

  
For example, I was able to generate this output using some fake data

and BY group processing:

That's not all the code, but it shows how to do the column spanning on a report row. You'd need to use the rest of the code for the beginning of the report table and then end of the report table by following the model set out in the user group papers.

Cynthia

And the good news is that this did work in ODS EXCEL, as shown below (except for the percent value which has the notorious number formatted as text green triangle). I tried TAGATTR with a percent format, but that did not work. So that would be a question for Tech Support.

Hope this helps,
Cynthia

[SASuserlot]

Thanks for taking time to ans my question. I really appreciate it. Is it possible for you to send me the complete code which you were did here based on fake data. that will helps me to refer and play around with my requirement.

[Cynthia_sas]

Here it is. You'll need to refer to the RWI documentation to find out how to use the RWI syntax.

Cynthia

 title; footnote;
    
 data fakedata;
  infile datalines dlm=',' dsd;
  input grp $ var1 var2 rdate : date. var3 var4 var5;
return;
datalines;
AAA, 50, 35, 01Mar2021,15,5,10
AAA, 60, 40, 15Mar2021,20,10,15
AAA, 110,75, 31Mar2021,.25,.,. 
BBB, 50, 35, 01Mar2021,15,5,10
BBB ,60, 40, 15Mar2021,20,10,15
BBB, 110,75, 31Mar2021,.25,.,.
;
run;

ods html path="c:\temp\output" file="col_span_row.html";
ods pdf file="c:\temp\output\col_span_row.pdf" ;
ods excel file="c:\temp\output\col_span_row.xlsx";

options missing = ' ';

title "Example Cell Spanning";
data _null_; 
  set fakedata end=last; 
  by grp;
  if _N_ = 1 then do; 
      dcl odsout obj(); 
      obj.table_start(); 
      obj.head_start(); 
	  ** Header row 1;
	  obj.row_start(type:"Header");
      obj.format_cell(text: "Column Spanning in the Header", column_span:7, style_attr:"color=black backgroundcolor=lightyellow"); 
      obj.row_end(); 
	  ** Header row 2;
      obj.row_start(type: "Header"); 
      obj.format_cell(text: "grp", style_attr:"color=black backgroundcolor=lightyellow fontweight=bold"); 
      obj.format_cell(text: "var1", style_attr:"color=black backgroundcolor=lightyellow fontweight=bold"); 
      obj.format_cell(text: "var2", style_attr:"color=black backgroundcolor=lightyellow fontweight=bold"); 
      obj.format_cell(text: "rdate", style_attr:"color=black backgroundcolor=lightyellow fontweight=bold"); 
      obj.format_cell(text: "var3", style_attr:"color=black backgroundcolor=lightyellow fontweight=bold"); 
      obj.format_cell(text: "var4",style_attr:"color=black backgroundcolor=lightyellow fontweight=bold"); 
      obj.format_cell(text: "var5",style_attr:"color=black backgroundcolor=lightyellow fontweight=bold"); 
      obj.row_end(); 
      obj.head_end(); 
    end;
  ** row for every obs;
	if last.grp=0 then do;
      obj.row_start(); 
      obj.format_cell(data: grp ); 
      obj.format_cell(data: var1); 
      obj.format_cell(data: var2); 
      obj.format_cell(data: rdate, format: 'mmddyy10.'); 
      obj.format_cell(data: var3); 
      obj.format_cell(data: var4); 
      obj.format_cell(data: var5); 
      obj.row_end(); 
    end;
    else if last.grp =1 then do;
      obj.row_start(); 
      obj.format_cell(data: grp ); 
      obj.format_cell(data: var1); 
      obj.format_cell(data: var2); 
      obj.format_cell(data: rdate, format: 'mmddyy10.'); 
      obj.format_cell(data: var3, format: 'percent8.' , column_span:3); 
      obj.row_end(); 
    end;
  if last then do; 
      obj.table_end(); 
    end; 
run; 
ods html close; 
ods pdf close;
ods excel close;

title; footnote;

[SASuserlot]

Thanks cynthia, reference code and the paper rally useful for my work. one more question to ur output. is it possible to group the grp variable using RWI. like in the image (highlighted)?

[Cynthia_sas]

Hi:
Yes, take look at my paper, I had a row spanning example. Again, you'll have to use BY group processing. That's similar to what I did in the Complex Reports paper. The challenge is that you'll need to know ahead of time the number of rows to span -- if there are always going to be 3 rows per group, that you can hardcode the number. But otherwise, you may need to pre-calculate the number of rows you have for every group. I'm in the middle of another project and can't fiddle with code today. But I think between the paper and the code I posted already you should be able to figure it out. I'd recommend getting it working with the code I posted and then moving to your data.
Cynthia

[SASuserlot]

Thanks for you response. I will try by trial and error referring to your code. Thanks again for taking your time to resolve this. Thanks again.