topic Re: Proc Report Merge Columns in SAS Programming

Proc Report Merge Columns

Ninja_turtle — Fri, 18 Aug 2023 21:02:34 GMT

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 🙂

Re: Proc Report Merge Columns

mkeintz — Fri, 18 Aug 2023 22:03:52 GMT

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

Help us help you.

Re: Proc Report Merge Columns

ballardw — Fri, 18 Aug 2023 22:57:42 GMT

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.

Re: Proc Report Merge Columns

Ninja_turtle — Sat, 19 Aug 2023 05:59:03 GMT

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 🙂

Re: Proc Report Merge Columns

Ninja_turtle — Sat, 19 Aug 2023 06:00:24 GMT

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.

Re: Proc Report Merge Columns

Kurt_Bremser — Sat, 19 Aug 2023 07:07:27 GMT

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

Re: Proc Report Merge Columns

Ksharp — Sat, 19 Aug 2023 10:48:51 GMT

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;

Re: Proc Report Merge Columns

Ninja_turtle — Sat, 19 Aug 2023 14:13:40 GMT

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

Re: Proc Report Merge Columns

Cynthia_sas — Sat, 19 Aug 2023 16:26:42 GMT

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.

Re: Proc Report Merge Columns

Ninja_turtle — Sat, 19 Aug 2023 17:47:39 GMT

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.

Re: Proc Report Merge Columns

ballardw — Sat, 19 Aug 2023 23:06:26 GMT

@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.

Re: Proc Report Merge Columns

Ksharp — Sun, 20 Aug 2023 07:29:28 GMT

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.

Re: Proc Report Merge Columns

Cynthia_sas — Sun, 20 Aug 2023 13:23:25 GMT

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

Re: Proc Report Merge Columns

Ksharp — Sun, 27 Aug 2023 10:32:16 GMT

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;

Re: Proc Report Merge Columns

Cynthia_sas — Tue, 29 Aug 2023 01:11:06 GMT

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