topic Re: Merge header names in PROC REPORT in SAS Programming

Merge header names in PROC REPORT

Zatere — Tue, 20 Feb 2024 12:34:42 GMT

Hi,

I have the following code:

proc report data=sashelp.class ;
column 
name
('measurements' age sex height weight);
define name /'Name'  
;
run;

that produces the below output:

I wondered if I could modify the output as:

As you may see, I have merged the header "Name" with the cell above it.

Any help would much be appreciated.

Thanks

Re: Merge header names in PROC REPORT

Cynthia_sas — Tue, 20 Feb 2024 13:19:18 GMT

Hi:
PROC REPORT does not merge headers vertically, as you show in your screen shot.
Cynthia

Re: Merge header names in PROC REPORT

Ksharp — Thu, 22 Feb 2024 03:06:11 GMT

You could try RWI skill.
Here is an example:

https://communities.sas.com/t5/SAS-Programming/How-to-create-report-with-varying-column-and-row-numbers-using/m-p/907985

Re: Merge header names in PROC REPORT

Cynthia_sas — Thu, 22 Feb 2024 14:07:47 GMT

Hi @Ksharp, yes, RWI is a possibility. I did not mention it because the original question did not specify what destinations in ODS they were using with their code.
Cynthia

Re: Merge header names in PROC REPORT

Kurt_Bremser — Thu, 22 Feb 2024 15:32:27 GMT

How would it be possible to move the "Name" into the first line?

Re: Merge header names in PROC REPORT

Zatere — Thu, 22 Feb 2024 15:40:42 GMT

Hi,

You could phrase it like that.

If possible, the end result would be like the below:

If one would do it in excel, they would just merge the two rows to put into the "Name".

Re: Merge header names in PROC REPORT

Ksharp — Fri, 23 Feb 2024 02:25:26 GMT

Here is an example for EXCEL destination.

data table3way;
  length dmtype durationbi $15;
  infile datalines dlm=',' dsd;
  input dmtype $ ordvar Durationbi $ Num cMean cSTD LowerCL UpperCL Prob_t;
return;
datalines;
"Type 1",1,"<=10 years",17,76.76,17.85,67.59,85.94,0.7243
"Type 1",2,">10 year",44,75.30,13.06,71.32,79.27,0.7243
"Type 2",1,"<=10 years",47,67.23,22.22,44.44,88.88,0.5584
"Type 2",2,">10 year",35,69.43,33.33,55.55,99.99,0.5584
;
run;


ods excel file='c:\temp\temp.xlsx';
data _null_; 
  set table3way end=last; 
  by dmtype;
  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: "Type", row_span:2, column_span: 1, style_attr:"vjust=m color=black backgroundcolor=yellow fontweight=bold"); 
      obj.format_cell(text: "Results", column_span:7, style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.row_end(); 
	  ** Header row 2;
      obj.row_start(type: "Header"); 
      obj.format_cell(text: "Durationbi", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "num", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "cmean", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "cstd", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "lowercl",style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "uppercl", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "probt",style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.row_end(); 
      obj.head_end(); 
    end;
  ** row for every obs;
  ** treat dmtype and prob_t differently so they span rows;

      obj.row_start(); 
      obj.format_cell(data: dmtype,row_span:1, style_attr:"vjust=m fontweight=bold" ); 
      obj.format_cell(data: Durationbi, row_span:1); 
      obj.format_cell(data: num, row_span:1); 
      obj.format_cell(data: cmean, row_span:1); 
      obj.format_cell(data: cstd, row_span:1); 
      obj.format_cell(data: lowercl, row_span:1); 
      obj.format_cell(data: uppercl, row_span:1); 
      obj.format_cell(data: prob_t, row_span:1, style_attr:"vjust=m fontweight=bold"); 
      obj.row_end(); 
   
  if last then do; 
      obj.table_end(); 
    end; 
run;
footnote;title;
ods excel close;