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.
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.
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.
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,
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.