Any new updates on Spanning columns in proc report?

statistician13 — Tue, 02 May 2017 20:15:44 GMT

I know you can span headers in proc report and I've read some 5 year old threads here indicating at that time that it wasn't possible to span data cells across columns in proc report. Is that still the case or has this changed? I'm hoping to span a number of data cells across columns (not at the header level).

Thanks.

Re: Any new updates on Spanning columns in proc report?

ballardw — Tue, 02 May 2017 22:16:04 GMT

I've managed to do something like that by writing raw html but I don't think that Proc Report has or will have that behavior. Consider that one of the main things for Report is to do sums and such at breaks. WHICH column would such a "data" value be in for inclusion in a group "total"?

Pretty much anything that spans over two or more columns or rows should be a categorical variable in most cases.

HOWEVER, going back to very old school with a data step youi can place characters anywhere on a page using PUT with the @, / and # column and line pointer controls.

Re: Any new updates on Spanning columns in proc report?

statistician13 — Tue, 02 May 2017 22:36:48 GMT

Thanks. Right, I'm trying to avoid writing raw HTML or RTF script. I may have a hack in RTF that I'm exploring now that would require just writing a single span statement. If I figured it out, i'll post back. Thanks for the info.

Re: Any new updates on Spanning columns in proc report?

Cynthia_sas — Tue, 02 May 2017 23:01:04 GMT

Hi,
PROC REPORT does not let you span columns in the data cells, but the new (9.4) Report Writing Interface will let you span data cells. See the example below (long code, spanning is shown in output #2).

cynthia

and the code is (note that you will see a message in the log that RWI is not supported for RTF -- I have not had issues with simple tables, but you might not get the results you want in RTF, the same way as in PDF and HTML):

 title; footnote;

ods html file="c:\temp\iris_span_data_cols.html";
ods pdf file="c:\temp\iris_span_data_cols.pdf" notoc startpage=no;
ods rtf file="c:\temp\iris_span_data_cols.rtf" startpage=no;

ods escapechar='~'; 

title '1) Partial Detail Report';
data _null_; 
  set SASHELP.IRIS(obs=6) end=last; 
  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: "Span 3 Header", column_span:3, style_attr:"color=black backgroundcolor=CXd98cb3"); 
      obj.format_cell(text: "Span Last 3", column_span:3,style_attr:"color=black backgroundcolor=CX2e852e"); 
      obj.row_end(); 
	  ** Header row 2;
	  obj.row_start(type:"Header");
      obj.format_cell(text: "Second Spanning Header", column_span:3,style_attr:"color=black backgroundcolor=CXd98cb3"); 
      obj.format_cell(text: "Another Header Row", column_span:3,style_attr:"color=black backgroundcolor=CX2e852e"); 
      obj.row_end(); 
	  ** Header row 3;
      obj.row_start(type: "Header"); 
      obj.format_cell(text: "Species", style_attr:"color=black backgroundcolor=CXd98cb3 fontweight=bold"); 
      obj.format_cell(text: "SepalLength", style_attr:"color=black backgroundcolor=CXd98cb3 fontweight=bold"); 
      obj.format_cell(text: "SepalWidth", style_attr:"color=black backgroundcolor=CXd98cb3 fontweight=bold"); 
      obj.format_cell(text: "PetalLength", style_attr:"color=black backgroundcolor=CX2e852e fontweight=bold"); 
      obj.format_cell(text: "PetalWidth", style_attr:"color=black backgroundcolor=CX2e852e fontweight=bold"); 
      obj.format_cell(text: "New Var",style_attr:"color=black backgroundcolor=CX2e852e fontweight=bold"); 
      obj.row_end(); 
      obj.head_end(); 
    end;
  ** row for every obs;
	  newvar = sum(of _numeric_);
      obj.row_start(); 
      obj.format_cell(data: species ); 
      obj.format_cell(data: SepalLength); 
      obj.format_cell(data: SepalWidth); 
      obj.format_cell(data: PetalLength); 
      obj.format_cell(data: PetalWidth); 
      obj.format_cell(data: newvar); 
      obj.row_end(); 
   
  if last then do; 
      obj.table_end(); 
    end; 
run; 
   

title '2) Partial Detail Report with spanning data cells';
data _null_; 
  set SASHELP.IRIS(obs=6) end=last; 
  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: "Span 3 Header", column_span:3, style_attr:"color=black backgroundcolor=CXd98cb3"); 
      obj.format_cell(text: "Span Last 3", column_span:3,style_attr:"color=black backgroundcolor=CX2e852e"); 
      obj.row_end(); 
	  ** Header row 2;
	  obj.row_start(type:"Header");
      obj.format_cell(text: "Second Spanning Header", column_span:3,style_attr:"color=black backgroundcolor=CXd98cb3"); 
      obj.format_cell(text: "Another Header Row", column_span:3,style_attr:"color=black backgroundcolor=CX2e852e"); 
      obj.row_end(); 
	  ** Header row 3;
      obj.row_start(type: "Header"); 
      obj.format_cell(text: "Species", style_attr:"color=black backgroundcolor=CXd98cb3 fontweight=bold"); 
      obj.format_cell(text: "SepalLength", style_attr:"color=black backgroundcolor=CXd98cb3 fontweight=bold"); 
      obj.format_cell(text: "SepalWidth", style_attr:"color=black backgroundcolor=CXd98cb3 fontweight=bold"); 
      obj.format_cell(text: "PetalLength", style_attr:"color=black backgroundcolor=CX2e852e fontweight=bold"); 
      obj.format_cell(text: "PetalWidth", style_attr:"color=black backgroundcolor=CX2e852e fontweight=bold"); 
      obj.format_cell(text: "New Var",style_attr:"color=black backgroundcolor=CX2e852e fontweight=bold"); 
      obj.row_end(); 
      obj.head_end(); 
    end;
	if _n_ = 1 then do;
	  obj.row_start(type:"Data");
      obj.format_cell(text: "A very important note", column_span:6,style_attr:"color=black backgroundcolor=white"); 
      obj.row_end(); 
	end;
  ** row for every obs;
	  newvar = sum(of _numeric_);
      obj.row_start(); 
      obj.format_cell(data: species ); 
      if sepallength lt 50 then do;
         obj.format_cell(data: SepalLength); 
         obj.format_cell(data: SepalWidth); 
	  end;
	  else if sepallength ge 50 then do;
	     tempvar = catx(' ','SL=',sepallength,'SW=',sepalwidth);
         obj.format_cell(data: tempvar,column_span:2, style_attr:"backgroundcolor=lightyellow"); 
	  end;
      obj.format_cell(data: PetalLength); 
      obj.format_cell(data: PetalWidth); 
      obj.format_cell(data: newvar); 
      obj.row_end(); 
   
  if last then do; 
      obj.table_end(); 
    end; 
run; 

ods _all_ close; 

title; footnote;

Re: Any new updates on Spanning columns in proc report?

statistician13 — Wed, 03 May 2017 17:00:32 GMT

This is amazing, Cynthia_sas. Thanks so much for this. I am working with 9.4 now and haven't yet dug into the report writing interface. I'm digging in now. Much appreciated. This is exactly what I'm looking for!

Re: Any new updates on Spanning columns in proc report?

statistician13 — Wed, 03 May 2017 17:02:43 GMT

By the way, after a cursory reviewer of the code below, I can't tell you how much this interace is going to improve my life! 😉 Since I try to automate all my code, and I have a need to span columns, this will be critical to those endeavor.

topic Re: Any new updates on Spanning columns in proc report? in ODS and Base Reporting

Any new updates on Spanning columns in proc report?

Re: Any new updates on Spanning columns in proc report?

Re: Any new updates on Spanning columns in proc report?

Re: Any new updates on Spanning columns in proc report?

Re: Any new updates on Spanning columns in proc report?

Re: Any new updates on Spanning columns in proc report?