The SAS Output Delivery System and reporting techniques

Any new updates on Spanning columns in proc report?

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

Any new updates on Spanning columns in proc report?

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.


Accepted Solutions
Solution
‎05-03-2017 01:00 PM
SAS Super FREQ
Posts: 8,868

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

[ Edited ]
Posted in reply to statistician13

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

span_rwi_data_cell.png

 

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;

View solution in original post


All Replies
Super User
Posts: 11,343

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

Posted in reply to statistician13

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.

Contributor
Posts: 57

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

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.
Solution
‎05-03-2017 01:00 PM
SAS Super FREQ
Posts: 8,868

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

[ Edited ]
Posted in reply to statistician13

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

span_rwi_data_cell.png

 

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;
Contributor
Posts: 57

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

Posted in reply to Cynthia_sas

This is amazing, .  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!

Contributor
Posts: 57

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

Posted in reply to Cynthia_sas

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.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 204 views
  • 0 likes
  • 3 in conversation