proc report merging across row headers without spilling data to the right (ods excel)

[RJB_NZ]

Hi,

When I run the below code, then the "Total of sex and age" merges across columns A and B. But then it pushes the Height and Weight to the right.

Does anyone know how to get row 13 to be column A=>"Total of sex and age"  B=>62 C=>100

Thanks

Rodney

data cars;
set sashelp.class;
run;

proc summary data=sashelp.class nway missing;
class sex age;
var Height Weight;
output mean=
out=class_sumry(drop=_type_ _freq_);
run;
proc summary data=sashelp.class nway missing;
var Height Weight;
output mean=
out=class_total(drop=_type_ _freq_);
run;

data ready;
length sex $50.;
set class_sumry
class_total(in=in_tot);
if in_tot then sex = 'Total of sex and age';
run;

ODS EXCEL File="c:\merge_test.xlsx"
style= MBUDatafileStyle
Options (absolute_column_width='10');

proc report data=ready missing;
column sex age height weight merge_total;
define sex / group order = data;
define age / group order = data;
define height / analysis sum format=comma.;
define weight / analysis sum format=comma.;
define merge_total / computed noprint;

*styles based on computed efforts;
compute merge_total;
if sex = 'Total of sex and age' then do;
call define(_row_, 'style', "style=[fontweight=bold]");
call define(1, 'style/merge', 'style=[tagattr="MERGEACROSS:2"]');
end ;
endcomp ;
run;
ODS EXCEL Close;

[Reeza]

The code explicitly does a merge across two columns. Did you want it merged across rows instead?
If you remove that line it goes in the first column, A. It's not wide enough to see though, because you've set the default column width to 10 which is too short. 

I've highlighted the lines of code that are problematic.

data cars;
set sashelp.class;
run;
proc summary data=sashelp.class nway missing;
class sex age;
var Height Weight;
output mean=
out=class_sumry(drop=_type_ _freq_);
run;
proc summary data=sashelp.class nway missing;
var Height Weight;
output mean=
out=class_total(drop=_type_ _freq_);
run;
data ready;
length sex $50.;
set class_sumry
class_total(in=in_tot);
if in_tot then sex = 'Total of sex and age';
run;

ODS EXCEL File="c:\merge_test.xlsx"
style= MBUDatafileStyle
Options (absolute_column_width='10');
proc report data=ready missing;
column sex age height weight merge_total;
define sex / group order = data;
define age / group order = data;
define height / analysis sum format=comma.;
define weight / analysis sum format=comma.;
define merge_total / computed noprint;
*styles based on computed efforts;
compute merge_total;
if sex = 'Total of sex and age' then do;
call define(_row_, 'style', "style=[fontweight=bold]");
call define(1, 'style/merge', 'style=[tagattr="MERGEACROSS:2"]');
end ;
endcomp ;
run;
ODS EXCEL Close;

Spoiler

---

@RJB_NZ wrote:

Hi,

When I run the below code, then the "Total of sex and age" merges across columns A and B. But then it pushes the Height and Weight to the right.

Does anyone know how to get row 13 to be column A=>"Total of sex and age"  B=>62 C=>100

Thanks

Rodney

data cars;
set sashelp.class;
run;

proc summary data=sashelp.class nway missing;
class sex age;
var Height Weight;
output mean=
out=class_sumry(drop=_type_ _freq_);
run;
proc summary data=sashelp.class nway missing;
var Height Weight;
output mean=
out=class_total(drop=_type_ _freq_);
run;

data ready;
length sex $50.;
set class_sumry
class_total(in=in_tot);
if in_tot then sex = 'Total of sex and age';
run;

ODS EXCEL File="c:\merge_test.xlsx"
style= MBUDatafileStyle
Options (absolute_column_width='10');

proc report data=ready missing;
column sex age height weight merge_total;
define sex / group order = data;
define age / group order = data;
define height / analysis sum format=comma.;
define weight / analysis sum format=comma.;
define merge_total / computed noprint;

*styles based on computed efforts;
compute merge_total;
if sex = 'Total of sex and age' then do;
call define(_row_, 'style', "style=[fontweight=bold]");
call define(1, 'style/merge', 'style=[tagattr="MERGEACROSS:2"]');
end ;
endcomp ;
run;
ODS EXCEL Close;

---

Just as an FYI you could do all summary stats in a single proc summary by removing the NWAY option and/or that PROC TABULATE may work better here as well. 

Not sure if that will scale to your actual use case though so just mentioning it for now. 

[RJB_NZ]

Thanks for your reply Reeza, you raise some good points.

The subject should probably be changed to "column headers" (from "row headers"). If I knew how, I would...

Yes I want the column width's limited. That's really important to fitting much more on the screen.

Yes the actual use case needs a lot of the functionality of proc report, so proc tabulate is not going to be useful for me 😞

As per the example, the Total description is much longer than all other categories, so would like it to show across both cells A13 & B13. How that happens I'm flexible with (phew). Merge across is one option (but that's currently inserting a cell, rather than merging). Getting Excel to show the cell A13 text across cell B13 is good too (as would happen if I manually made this spreadsheet in Excel!)

[Reeza]

Does anyone know how to get row 13 to be column A=>"Total of sex and age"  B=>62 C=>100

---

Getting Excel to show the cell A13 text across cell B13 is good too (as would happen if I manually made this spreadsheet in Excel!)

---

From my knowledge/experience with Excel if you have anything in the B cell then it will not overflow. If you have a blank cell in B then it will default overflow the cell. 

Can you attach an Excel file that shows what you'd like and how it's accomplished manually?

[RJB_NZ]

Sure

[Reeza]

So when you have a numeric missing SAS writes a period to that cell, which means it has something in it. However, if the variable is a character cell it's shown as missing. 

Converting that first column to character and suppressing the total seems to work. It seems like it should also be possible to do that via PROC REPORT/COMPUTE to set that column to missing. No idea how to do that in PROC REPORT personally though 😞

data cars;
set sashelp.class;
run;

proc summary data=sashelp.class missing;
class sex age;
var Height Weight;
output mean=
out=class_sumry(drop= _freq_);
run;


data ready;
length sex $50.;
set class_sumry;
if _type_ in (0, 3);

if _type_ =0 then sex = 'Total of sex and age';
if not missing(age) then age_char = put(age, $8. -l);

drop age _type_;
run;


option missing = '';
ODS EXCEL File="/home/fkhurshed/Demo1/merge_test.xlsx"
Options (absolute_column_width='10');

proc report data=ready missing;
column sex age_char height weight merge_total;
define sex / group order = data;
define age_char / group order = data;
define height / analysis sum format=comma.;
define weight / analysis sum format=comma.;
define merge_total / computed noprint;

*styles based on computed efforts;
compute merge_total;
if sex = 'Total of sex and age' then do;
call define(_row_, 'style', "style=[fontweight=bold]");
*call define(1, 'style/merge', 'style=[tagattr="MERGEACROSS:2"]');
*or add something here to ensure that age is missing - not sure how you can do that;
end ;
endcomp ;
run;

ods excel close;

[Cynthia_sas]

Hi:

  You may need to work with Tech Support on the mergeacross issue. It seems to me that the Excel TAGATTR instruction is happening after PROC REPORT makes the cells in the final row. That may be the expected behavior, I don't know because I don't use the MERGE in Excel much at all.

  However, to simplify your code considerably, you don't need any of your PROC MEANS setup to make the "READY" file or the DATA step program. PROC REPORT will calculate everything in one step using the original SASHELP.CLASS data as shown below:

  Since the SEX variable is a $1 in length, if you want a longer text string on the final break line, you need a computed variable that will have a larger defined length. I call this computed variable DISPSEX and at the break, I give that cell a long string as the last value. To highlight the line, I changed the summary style to be consistent with the Header style attribute and made the font_weight attribute bold. Since I did not have your custom style template, I didn't write the output to Excel or use your absolute_column_width setting or do the MERGEACROSS. 

 Rather than focusing on Excel,  I wanted to provide more streamlined code that didn't make so many passes through the data just to get the mean and the overall mean. PROC REPORT can calculate all the same statistics that PROC TABULATE and PROC MEANS can calculate -- all the big ones at least -- MIN, MAX, MEAN, MEDIAN, CSS, STD, N, you can look up the rest of the statistics in the REPORT doc here: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p1hagdk7qeis65n1659yii98d8s1.htm .

  Hope this helps you understand a bit more how PROC REPORT can generate summary statistics.

Cynthia

[Reeza]

If you're open to other options I'd consider using the FLOW option so that the text wraps into two lines if that's an acceptable solution.

[RJB_NZ]

No, looks are important here. So I'd like the whole description on the one line...

[xxformat_com]

Hi,

I would use something like this. If you want the text to be display on a single line, just increate the width attribute value.

But I'm not sure how to merge both cells in the total part tho.

I hope it helps.

proc format;
    value $sex_t 'M'='Male'
                 'F'='Female'
                 'T' = 'Total of sex and age';
run;

ods excel file="&xxtraining./reporting/test.xlsx" options(flow='table');

proc report data=sashelp.class spanrows;* missing;
    column sex age height weight;
    define sex    / group style(column)=[width=2cm verticalalign=middle] format=$sex_t. ;
    define age    / group style(column)=[width=2cm textalign=left];
    define height / analysis;
    define weight / analysis;

    rbreak after  / summarize;

    compute after;
        sex='T';
        call define(_row_, 'style', "style=[fontweight=bold]");
    endcomp;
run;

ods excel close;