Merging Cells horizontally in PROC REPORT

[SGB]

Hi SAS-perts

My colleague is working on a reports which requires to merge the cells of PROC REPORT.

We are working with SAS EG and the valid destinations are HTML and PDF only.

I have attached the model report!

Any suggestions and inputs are appreciated.

Thanks

BG

[Cynthia_sas]

Hi:

  Although you can perform the color coding that you show, PROC REPORT does not perform "cell merging" such as is possible with Excel. You can use custom styles to change the color of the border lines between cells, but this is really not the same as "merging" cells. So if your first row had 5 cells, all with the same value, even if you changed the color of the border lines, there would still be 5 distinct cells, not one big cell that spanned 5 columns.

cynthia

[SGB]

Thanks for your reply. Is there any other procedure.. such as proc template.. can handle this?.

Thanks again for your time.

[Cynthia_sas]

Hi:

  When SAS produces tabular reports -- no matter what procedure you use -- whether PROC PRINT, PROC REPORT, PROC TABULATE, PROC MEANS or even PROC TEMPLATE (for tables) -- SAS really wants to produce output where the there are "header" cells and there are "data" cells.  Generally, if spanning is going to occur, it will occur in the header areas (column header and or row header area), but spanning (what you call merging) will not occur in the "data" cells or "data" area.

  For example, consider this report shown in the screenshot, example_header_spanning.png -- it has a LOT of spanning or seemingly merged cells in the column headers and in the row header (everything with blue background). But in the "white" area -- for the "data" cells, there is a cell value for every column. So, for example, I could not just "merge" the cells that contain  the values for "Joyce" and "51.3". Joyce belongs in the NAME column and 51.3 belongs in the HEIGHT column. And, even if you had, by chance, 51.3 for HEIGHT and 51.3 for WEIGHT, on the same row, SAS would want to show those 2 values separately there's no way to "merge" the 2 values into 1 extra wide cell.

  The second screenshot shows an example of your type of "calendar" or schedule with some colors (but not all) based on the values in the cell -- I just picked some random colors for some of the values. As you can see, if the value for "VVVVV" appears in several different datacells, each cell can have the same background color, but PROC REPORT won't "merge" the cells. That's because the intersection of a report row and a report column should be filled in with some calculated value or the value for a variable from your data. Even though PROC TEMPLATE will allow you to define a custom TABLE template, you could still only define spanning in the header area. And with a custom style template, as I said, you could make the border lines the same color as the cell background, but you would still have separate and distinct cells instead of 1 merged cell.

  It might be possible with a GRAPH procedure and/or ANNOTATE to draw the entire grid with the appearance of merged cells, but that seems a lot of work and a fairly steep learning curve.

cynthia

[SGB]

Hi Synthia

At last we did this using Gchart - Horizontal Barchart with subgroups with annotation - to complete this task. Was a good learning process for both of us.

Thanks a lot for your help.

SGB

[Cynthia_sas]

Oh, good, I'm happy it worked out for you. Using a Horizontal Bar Chart was a clever solution.

cynthia

[SASuserlot]

Hi SGB, I need the same requirement as you done. is it possible to provide some insite or sample code how you achieved merging cells horizontally!

[Cynthia_sas]

Hi:

  In PROC REPORT, you can only merge header cells horizontally. If you move into the Report Writing Interface (RWI), then you can merge cells horizontally, but it is a DATA step solution using the method/object syntax. There is an example and some code in this Forum posting: https://communities.sas.com/t5/ODS-and-Base-Reporting/How-to-Merge-cells-horizontally-in-ODS-Excel-o...

The posted code is written for ODS HTML, ODS PDF and ODS EXCEL.

Hope this helps,

Cynthia