BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Daily1
Quartz | Level 8
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

Daily1_0-1655980021534.png

i want result 

Daily1_1-1655980072927.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

4 REPLIES 4
Ksharp
Super User

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
Diamond | Level 26

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

Cynthia_sas_0-1655989468444.png

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

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

Daily1_1-1656048931078.png

 

 

Cynthia_sas
Diamond | Level 26

Hi:

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

Cynthia_sas_0-1656111428909.png

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1413 views
  • 4 likes
  • 3 in conversation