The SAS Output Delivery System and reporting techniques

How to merge cells in PROC REPORT

Reply
Contributor
Posts: 43

How to merge cells in PROC REPORT

Currently I am creating rtf report.

Is it possible to give out a report like below?

AA1 | BB1 | CC1 | DD1
AA2 | Merged | DD2
AA3 | BB3 | CC3 | DD3

Any comment will be appreciated.
SAS Super FREQ
Posts: 8,642

Re: How to merge cells in PROC REPORT

Hi:
To make a report SAS builds tables. SAS builds the report tables to have the same number of columns on every report row, There are some instances where you can stack columns or span rows, but the kind of column spanning that SAS does, it generally does in the column headers. For example, this is possible:
[pre]
| Span Hd1 | Span Hd2 |
|HDR1|HDR2 |HDR3 |HDR4 | <--- report headers
------------------------
|AA1 | BB1 | CC1 | DD1 | < ---report rows
|AA2 | BB2 | CC2 | DD2 |
|AA3 | BB3 | CC3 | DD3 |
[/pre]

Note how the string "Span Hd1" spans HDR1 and HDR2 in the report header area. There are syntax methods in PROC REPORT and PROC TABULATE for example to create these spanning headers. There are not syntax methods to "merge" cells, so that you truly have 1 cell that occupies the same space as the 2 cells above and/or below it.

What you can do, however, is "disappear" the border between cells, by selectively changing the border color to be the same as the background of the table. The program below shows one method (using SAS 9.2). By turning the left side border color to white between the AGE and the HEIGHT variables, it appears as though there is one cell. However, as you can see, both AGE and HEIGHT on the row for Mary have values and those values each stay in their respective cell areas.

There might possibly be an RTF control string method (a way by which you could pass an RTF control string to perform merging) to achieve merged cells, but I know only a few RTF control strings (like \b, \line, \tab and \u) and not much beyond that. You might wish to check with Tech Support to see whether they know of any methods to achieve merged cells using ODS RTF.

cynthia

[pre]
ods rtf file='c:\temp\chgborder.rtf';
proc report data=sashelp.class nowd;
column ("Span All Headers" name ('Span Hd2' age height) weight);
define name / order;
define age / display;
define height / display;
define weight / display;
compute height;
if name = 'Mary' then do;
call define(_col_,'style','style={borderleftcolor=white}');
end;
endcomp;
run;
ods rtf close;
[/pre]
Contributor
Posts: 43

Re: How to merge cells in PROC REPORT

Cynthia,

Thank you so much for the explanation.
It appears that SAS 9.2 has introduced many interesting styles. I will have a try.

-Kevin
Contributor
Posts: 30

Re: How to merge cells in PROC REPORT

Hi Kevin

This was one of the questions I ponder on ODS RTF several years ago. Others have this same question.
http://support.sas.com/forums/message.jspa?messageID=8740#8740


But a solution to this is not as complicate as you/others think:
http://www.lexjansen.com/phuse/2007/po/po07.pdf

Here are some output samples:
http://www.tranz.co.uk/userimages/Phuse2007_PO07_RiTEN_Download.pdf





Regards
Duong
www.tranz.co.uk
Occasional Learner
Posts: 1

Re: How to merge cells in PROC REPORT

Capture.PNG

Hi Duong,

 

I want to merge cells top and bottom instead of side by side as shown.

The solution you provided to Kevin doesn't seem it would merge how I want. Do you have any good idea?

 

Thank you!

lnnagasaka

 

SAS Super FREQ
Posts: 8,642

Re: How to merge cells in PROC REPORT

Hi: You have just reopened a post from 2009. If you have a NEW question, which you seem to have, then it would be better for you to make a new post instead of reactivating a 7 year old post. You can always put a link to the related post in your new post.

 

To do what you show in your picture, you would either have to make your total columns group or order variables or you would have to investigate the new Report Writing Interface. Creating this type of merged data cells is not something you can normally do with PROC REPORT or PROC TABULATE.

 

If you want folks to help you with a solution, you need to post your data and the code you've tried. With a request like this the structure of the input data will make a difference in the solution.

 

Without a sample of your input data and the code you've tried, other folks will just be guessing and spinning their wheels.

 

cynthia

Post a Question
Discussion Stats
  • 5 replies
  • 3619 views
  • 1 like
  • 4 in conversation