BookmarkSubscribeRSS Feed
SGB
Obsidian | Level 7 SGB
Obsidian | Level 7

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

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

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
Obsidian | Level 7 SGB
Obsidian | Level 7

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

Thanks again for your time.

Cynthia_sas
SAS Super FREQ

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
SGB
Obsidian | Level 7 SGB
Obsidian | Level 7

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
SAS Super FREQ


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

cynthia

SASuserlot
Barite | Level 11

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
SAS Super FREQ

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 10987 views
  • 5 likes
  • 3 in conversation