topic Re: Proc report SPANROWS for multiple columns with different groupings in ODS and Base Reporting

Proc report SPANROWS for multiple columns with different groupings

bstarr — Mon, 30 Oct 2017 22:48:14 GMT

Hello,

I am trying to create a report that has multiple columns with merged rows. I'm using the SPANROWS option to merge repeating values, but PROC REPORT seems to be splitting "higher" level (less granular) groups based on "lower" level (more granular) groups that appear earlier in the COLUMN statement.

The below code replicates my issue:

data test;
input group $ value summary;
cards;
apple 4 4.25
apple 2 4.25
banana 6 4.25
banana 5 4.25
;
run;

proc report data=test nowd spanrows;
    columns group value summary;
        define group / group;
        define value / display;
        define summary / group;
run;

I would like to generate the report such that the rows for "summary" are spanned across all rows (so 4.25 appears only once and the 4 rows are merged), not just the first two and the last two. I can get it to work by moving the "summary" column before "group", but of course, I want summary to appear at the right of the table.

Does anyone know of any tips to generate a report this way? Is it even possible?

I am running SAS 9.4 TS1M4.

Thank you!

Re: Proc report SPANROWS for multiple columns with different groupings

Cynthia_sas — Tue, 31 Oct 2017 13:58:11 GMT

Hi:

Well, you might not be able to do this with PROC REPORT. There are other alternatives, like the Report Writing Interface. But the structure and rules of the data will make a difference. For example, what about KIWI in this revised set of data? Is this ever possible? Or what about oranges? There are 3 rows -- in your data, you always show 2 rows. Is the data "pre-summarized"? Are there always 2 rows? could the same group have more than 1 summary?

cynthia

apple 4 4.25
apple 2 4.25
banana 6 4.25
banana 5 4.25
kiwi 4 4.44
kiwi 2 2.22
kiwi 1 2.22
oranges 7 3.33
oranges 6 3.33
oranges 5 3.33

Re: Proc report SPANROWS for multiple columns with different groupings

bstarr — Tue, 31 Oct 2017 15:06:10 GMT

Hi Cynthia,

Thanks for your reply. In my particular data, I will always have 4 rows total, divided into 2 groups with 2 rows each. However, I could see use of this extending beyond my data to something more along the lines of your example (although there would likely only be 1 summary per group or over multiple groups).

Yes, my data are pre-summarized - I am essentially using PROC REPORT as a glorified PROC PRINT. I have tried using the summary capabilities in PROC REPORT and ran into other issues with getting the report layout how I want. In line "apple 4" of my example the 4 will actually be a mean in the report I am trying to build and in line "apple 2" the 2 will actually be a count (and the 4.25 will be a result from statistical significance testing), and I was having difficulties bringing the count a row down into the same column as the mean.

Another alternative that I will likely do is to generate the report close to what I want, then manually merge cells and make other formatting changes in the final ODS RTF output.

Thanks,

Brian

Re: Proc report SPANROWS for multiple columns with different groupings

Cynthia_sas — Tue, 31 Oct 2017 16:16:59 GMT

Hi, well, it would be easiest to do in PROC REPORT if you just moved SUMMARY to the left of VALUE. Otherwise, when you have SUMMARY on the end of the report row, PROC REPORT will not span because each unique VALUE now gets its own SUMMARY cell. Compare these 2 outputs using your original data:

title '1) summary before value';
title2 'since summary is the same for all values, you get spanning';
proc report data=test spanrows;
  column group summary value;
  define group / group;
  define summary / group;
  define value / order order=data;
run;

title '2) summary after value';
title2 'since each summary is now unique for each value, you get a cell for each value';
proc report data=test spanrows;
  column group value summary ;
  define group / group;
  define value / order order=data;
  define summary / group;
run;

PROC REPORT does build the report row from left to right and so, when you have the VALUE column with 2 unique values, that forces the SUMMARY column to be filled for each of the unique values.

cynthia