proc report column merge

[Daily1]

ODS excel file = "c:\MyFolder\Shoes Test.xlsx";

proc report data=sashelp.shoes;
	column ('region' '' region ) ('Product' '' Product) ('Subsidiary' '' Subsidiary)
('values' (("Sales " Sales) ('Inventory' Inventory) ('Returns' Returns)));
	define Region / '' order=data;
	define Product / '' order=data;
	define Subsidiary / '' order=data;
	define Sales / '' order=data;
	define Inventory / '' order=data;
	define Returns / '' order=data;

run;

ods excel close;

i have result

i want result 

[Ksharp · Accepted solution]

proc report data=sashelp.shoes nowd;
	column ('region' '' region ) ('Product' '09'x Product) ('Subsidiary' '09'x Subsidiary)
('values' (("Sales " Sales) ('Inventory' Inventory) ('Returns' Returns)));
	define Region / '' order=data;
	define Product / '' order=data;
	define Subsidiary / '' order=data;
	define Sales / '' order=data;
	define Inventory / '' order=data;
	define Returns / '' order=data;

run;

[Cynthia_sas]

Hi:

  An alternate approach would be to use the ODS ESCAPECHAR with the NEWLINE function as shown below in the browser (taking the default output):

  My preference is for the empty cells to be on the header row immediately above the data, as shown in the second example. I think it looks better that way. But the first example is closer to what you posted. ODS does not merge cells vertically, so unless you fiddle with borders, you can't merge the header cells in ODS. If you truly want merged cells for those headers, you'd need to use something more like the Report Writing Interface.

Cynthia

[Daily1]

if it's possible to merge cell vertically on this type result 

[Cynthia_sas]

Hi:

  If you want HTML or PDF type of output, then you can use the Report Writing Interface to generate a result like this:

The Report Writing Interface (RWI) supports row_span and column_span options and basically allows you to have a high-level of control over your output. However, right now, the RWI is only supported in PDF and HTML destinations and not supported by ODS RTF or ODS EXCEL or ODS WORD. So that is a tradeoff. So if you can live with PDF or HTML output, then yes, you can do what you want. Here's the code that generated the above example.

Cynthia

 title; footnote;
 options nodate nonumber;
     
ods pdf(id=1) file='c:\temp\use_rwi_shoes.pdf';
ods html(id=2) path='c:\temp' file='use_rwi_shoes.html';
ods escapechar='^'; 
   
title '1) Detail Report';
data _null_; 
  set sashelp.shoes(obs=10) 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: "Region", row_span:2,style_attr:"vjust=b fontweight=bold"); 
      obj.format_cell(text: "Product", row_span:2, style_attr:"vjust=b fontweight=bold"); 
      obj.format_cell(text: "Subsidiary", row_span:2, style_attr:"vjust=b fontweight=bold"); 
      obj.format_cell(text: "Values", column_span:3,style_attr:"just=c fontweight=bold"); 
      obj.row_end(); 
	  ** Header row 2;
      obj.row_start(type: "Header"); 
      obj.format_cell(text: "Sales", style_attr:"fontweight=bold"); 
      obj.format_cell(text: "Inventory", style_attr:"fontweight=bold"); 
      obj.format_cell(text: "Returns", style_attr:"fontweight=bold"); 
      obj.row_end(); 
      obj.head_end(); 
    end;
  ** row for every obs;
      obj.row_start(); 
      obj.format_cell(data: Region ); 
      obj.format_cell(data: Product); 
	  obj.format_cell(data: Subsidiary);
      obj.format_cell(data: Sales); 
      obj.format_cell(data: Inventory); 
      obj.format_cell(data: Returns); 
      obj.row_end(); 
   
  if last then do; 
      obj.table_end(); 
    end; 
run; 
ods pdf(id=1) close;
ods html(id=2) close;