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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

5 REPLIES 5
ballardw
Super User

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.

statistician13
Quartz | Level 8
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.
Cynthia_sas
SAS Super FREQ

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;
statistician13
Quartz | Level 8

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!

statistician13
Quartz | Level 8

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.

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