Proc Report Merge Columns

[Ninja_turtle]

Hi All,

I want to megre column A,B and C to make a single define column in proc report.

Tried to use the tagset but it didn't worked. For example I want the first column to be represented as Single merged column in xml file for A,B and C column and then the next column "Summary" starts from D column.

Any suggestion how to combine mutiple columns to represent single column value in proc report?

Thanks 🙂

[mkeintz]

1. I see no column A, B, or C.
2. Please show the code that generated the table you have displayed.

Help us help you.

[Ninja_turtle]

Code:

data have;
input condition $ Today Yesterday Diff;
datalines;
AAA 18 17 1
BBB 3524 3541 -17
CCC 773 781 -8
DDD 1721 1721 0
EEE 26 26 0
FFF 5 3 2
;
run;

ods
PROC REPORT DATA=have;
COLUMN condition Today Yesterday Diff;
run;

When i do a ods for this the conditional column is populated on column A, but i want to merge it like the screesshot below. Where conditional column comes in A,B,C column as one column and gets merged.

Thanks 🙂

[ballardw]

Example data

Your Proc report code

A clear description of the output.

Personally from that picture I see nothing it would make sense to 'merge columns". You also don't have any column that looks like "Summary.

[Ninja_turtle]

Code:

data have;
input condition $ Today Yesterday Diff;
datalines;
AAA 18 17 1
BBB 3524 3541 -17
CCC 773 781 -8
DDD 1721 1721 0
EEE 26 26 0
FFF 5 3 2
;
run;

ods
PROC REPORT DATA=have;
COLUMN condition Today Yesterday Diff;
run;

When I do ods for this the condition column is populated on column A, but I want to merge it like the screenshot below. Where condition column comes in A, B,C columns as one column and gets merged.

[Kurt_Bremser]

I think @Cynthia_sas will have an idea how to achieve this.

[Cynthia_sas]

Thanks, @Kurt_Bremser for your faith in me. PROC REPORT won't do what this person wants to do and it doesn't make sense. It is possible to make column A wider in Excel and to center the value, in PROC REPORT without spanning the columns as shown. I'll post an example. To really merge the A,B,and C columns horizontally, they would need a DATA step and the Report Writing Interface. But if the goal is to just make the Condition cell wider and centered, that is possible without RWI.
Cynthia

As an example, just using STYLE overrides, it is possible to alter the appearance of the CONDITION column in Excel without merging cells, as shown below:

I don't understand the purpose of merging the 3 columns so this would be my suggested approach. If it is absolutely critical for the 3 columns to be merged horizontally, then the DATA step and RWI approach will be needed.

[Ninja_turtle]

Thank You @Cynthia_sas  the reason why I want to merge 3 columns is what my buisness is expecting they have weird choices but that it is they want the first column to span into 3 columns(A,B,C) and the subsequent column should start from D.  If this is not possible in proc report how can we do it in data step? Though I am specifically looking for proc report.

[Ksharp]

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;

options orientation=portrait;

ods excel file="c:\temp\spantest.xlsx"   ;

title 'How to span Header Rows amd Data Rpws with RWI and DATA step';
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:1, column_span: 3, style_attr:"vjust=m color=black backgroundcolor=cxdddddd fontweight=bold"); 
      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, column_span: 3, 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;

[Ninja_turtle]

Thank You for solution, but I am specifically looking for proc report way as i have a very compled xml file being created and want to just do this change

[ballardw]

---

@Ninja_turtle wrote:

Thank You for solution, but I am specifically looking for proc report way as i have a very compled xml file being created and want to just do this change

---

Can you explain how Proc Report is the only way that an XML file can be created? I am not seeing any connection.

If you are using the OUT= option of Proc Report to create a data set for creating an XML then the data set would not have any concept of spanning 3 columns.

I also have a hard time seeing an XML that is concerned with spanning 3 columns.

[Ksharp]

I think it is really hard for PROC REPORT to spanning 3 columns like the picture you posted.
Anyway, my code is stolen from @Cynthia_sas , maybe She knew the solution of PROC REPORT.

[Cynthia_sas]

Hi,
As I already explained in my previous posting, It is NOT possible to do what the original poster asked for with PROC REPORT. The only way is as @Ksharp showed using DATA step and Report Writing Interface. Again, I don't understand the business need for columns A, B and C in Excel to be merged, but PROC REPORT can only merge header cells, not data cells. So you could make A empty, put the CONDITION variable in B and make C empty and then have a header spanning all 3 of those columns, but that is only going to merge the header cells. The data values cells will still remain 3 distinct cells in 3 distinct columns. You can't truly "merge" data cells as you can do in Excel or in the Report Writing Interface.
To me, this is the "rock and hard place" point of working with arbitrary cosmetic requirements like this. If the requirement is truly that this must be done with PROC REPORT, then you will need to go back to the requesters and explain that it is not possible, show them the alternative and ask what business purpose the requirement serves. It sounds to me as though they might have some other process that expects the TODAY column to start in Column D. If that is the case, there are a few ways to do THAT with PROC REPORT, but merging the first 3 columns with PROC REPORT isn't one of the ways.
Cynthia

[Ksharp]

Hi @Cynthia_sas   @Ninja_turtle   ，

I found the solution of PROC REPORT, Check it.

ods excel file='c:\temp\temp.xlsx';
proc report data=sashelp.class nowd;
define name/style(header column)={tagattr='mergeacross:3'};
run;
ods excel close;

[Cynthia_sas]

That's cool! I did not realize that mergeacross would work on Data cells as well as Header cells. That's really using an EXCEL format/style as a TAGATTR style override for ODS, which would only work for an ODS EXCEL file. Would not work for PDF or RTF or other destinations.
Cynthia