Help using Base SAS procedures

Merging Cells horizontally in PROC REPORT

Reply
Contributor SGB
Contributor
Posts: 41

Merging Cells horizontally in PROC REPORT

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

SAS Super FREQ
Posts: 8,868

Re: Merging Cells horizontally in PROC REPORT

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

Contributor SGB
Contributor
Posts: 41

Re: Merging Cells horizontally in PROC REPORT

Posted in reply to Cynthia_sas

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

Thanks again for your time.

SAS Super FREQ
Posts: 8,868

Re: Merging Cells horizontally in PROC REPORT

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


report_examp_some_colors.pngexample_header_spanning.png
Contributor SGB
Contributor
Posts: 41

Re: Merging Cells horizontally in PROC REPORT

Posted in reply to Cynthia_sas

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


SAS Super FREQ
Posts: 8,868

Re: Merging Cells horizontally in PROC REPORT


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

cynthia

Ask a Question
Discussion stats
  • 5 replies
  • 3732 views
  • 5 likes
  • 2 in conversation