The SAS Output Delivery System and reporting techniques

ODS RTF: How can I indicate SAS to merge specified cells of a table?

Reply
N/A
Posts: 0

ODS RTF: How can I indicate SAS to merge specified cells of a table?

Hi all,

I create statistical tables using ODS RTF (V8.2).

I have a table with 2 columns (Treatment 1 and Treatment 2).
I have to put a statistical results which is common with the two columns.

How can I indicate SAS to merge specified cells of a table (using Control words,...?) ?


I would be very glad if you can help me!

Many thanks!

Violaine
SAS Super FREQ
Posts: 8,868

Re: ODS RTF: How can I indicate SAS to merge specified cells of a table?

Posted in reply to deleted_user
Hi:
What you want to do can be accomplished several different ways:
1) you can concatenate the variables together in a DATA step and then create a new variable that has both values -- then use PROC PRINT, PROC REPORT or a DATA step program to write out your report;
2) you can concatenate the variables together in PROC REPORT; or
3) if the statistics are coming from a SAS procedure, you can change the table template associated with that procedure (a more advanced solution -- AND you have to want the data as it comes directly from the procedure -- without any further processing.

The following program illustrates #2:
[pre]
ods rtf file='mergecell.rtf';

proc report data=sashelp.class nowd;
column name age height weight newcol;
define name / order;
define age /display;
define height /display;
define weight /display;
define newcol /computed "Height***Weight"
style(column)={just=r};
compute newcol /character length=25;
newcol = put(height,4.1)||'***'||put(weight,5.1);
endcomp;
run;
ods rtf close;
[/pre]
If you run the program once, you will see that the values for Height and Weight together occupy the "NEWCOL" column, separated by '***'. If you change the define statements for height and weight in a subsequent run to use the NOPRINT option:
[pre]
define height /display noprint;
define weight /display noprint;
[/pre]
...then the only column you will see on the report is the NEWCOL column.

If you need help with #3 approach, your best bet is to contact SAS Technical Support.

Good luck,
cynthia
N/A
Posts: 0

Re: ODS RTF: How can I indicate SAS to merge specified cells of a table?

Posted in reply to Cynthia_sas
Many thanks but... I was certainly not enough precise.

What I want is to merge the 2 cells because the p-value in common for the 2 trt groups, and the p-value is not specified for the trt Group 1:

Group 1 (N=xx) Group 2 (N=xx)

2( <1%) 3( 2%)
12( 12%) 20( 12%)
102( 20%) 101( 11%)
P=0.123

When I build the table, I put the p-value in the Group 1 column.


Group 1 (N=xx) Group 2 (N=xx)

2( <1%) 3( 2%)
12( 12%) 20( 12%)
102( 20%) 101( 11%)
     P=0.123

But, when I print the results: I need to merge cells (group1 & group2).

After having this SAS table, I put this table in a proc print using an ODS RTF destination.

The problem is: I have already used your method #1 for the construction of columns 'Group1' and 'Group2' and these columns need to be centered.
So I can't used an other time this method in order to have the P-value in the middle of the group1 and group2 columns as I can't concatenate Group1 & Group2 columns.

If you have an other idea...

Many thanks!

Violaine
SAS Super FREQ
Posts: 8,868

Re: ODS RTF: How can I indicate SAS to merge specified cells of a table?

Posted in reply to deleted_user
Hi, Violaine:
I see what it is that you want to do, however, ODS RTF and PROC REPORT will not get you there in the current implementation. There is no column spanning the way you want.

IF it can be done with RTF control strings (and that's a big IF), then your best bet is to contact Tech Support for help. I have played around with RTF control strings, but mostly for tabs and spaces and line feeds.

Good luck,
cynthia
N/A
Posts: 0

Re: ODS RTF: How can I indicate SAS to merge specified cells of a table?

Posted in reply to Cynthia_sas
Many thanks cynthia, I will try to contact Tech Support!

Violaine
Contributor
Posts: 30

Re: ODS RTF: How can I indicate SAS to merge specified cells of a table?

Posted in reply to deleted_user
Hi Both

SAS cannot do this at the moment but it can be done
very easily with post-processing your RTF file (if you understand RTF).


Duong
N/A
Posts: 0

Re: ODS RTF: How can I indicate SAS to merge specified cells of a table?

How can I do that? I am very interested!

Violaine
SAS Super FREQ
Posts: 8,868

Re: ODS RTF: How can I indicate SAS to merge specified cells of a table?

Posted in reply to deleted_user
Violaine:
In order to post process the RTF file you must
1) understand the RTF string that you are currently generating (this involves creating an RTF file that is the closest to the one you want and then examining the file in Notepad)
2) understand the RTF string that you want to have (research the RTF spec)
3) write a SAS program to read in the RTF file after it's been created
3a) scan the RTF file for the string of interest -- line by line
3b) change the string of interest to the new string (when you find it)
3c) write file back out to a new file
4) open the NEW RTF file and make sure you have achieved your desired results

The reason I do not generally recommend this method is that #1 and #2 are a fairly large undertaking. If you do NOT understand RTF control strings or get your code exactly right for doing the substitution, then you can very, very easily corrupt the new RTF file and make it unopenable. (a single missing { or poorly placed \ will corrupt the file).

For example, when you use the JOURNAL style in RTF, you get a single line at the top and bottom of the table and a single line underneath the column headers. It is possible to change the single line under the column headers to a double line, but you have to know that the single line control string is:
\clbrdrb\brdrs

and that the double line string would be:
\clbrdrb\brdrdb


I do not know RTF well enough to be able to tell you what the spanning/merging syntax would be. However, you can experiment with the double line post processing program in this program:
[pre]

** this is the file for #1;
ods rtf file='c:\temp\freq.rtf' style=journal;
ods noproctitle;
proc freq data=sashelp.shoes;
tables region;
run;

proc print data=sashelp.class;
run;
ods rtf close;

** freq.rtf is from #1;
** dblout.rtf is the NEW RTF file -- for #4;
filename sngl_in "c:\temp\freq.rtf";
filename dbl_out "c:\temp\dblout.rtf";

** this is the program for step #3 -- you MUST understand the RTF;
** control strings in order for this to work.;
** upon my examination of the file, I determined that the place where I needed;
** to change my string was on a line where the length was GT 23 AND I had;
** to make sure I got the line under the column headers and not the line at the;
** bottom of the table.;
data _null_;
infile sngl_in lrecl=4096 recfm=v length=ln;
file dbl_out lrecl=4096 recfm=v;
input;
where_bdr = index(_infile_,'clbrdrb');
** make sure that you have a "long" line and that the clbrdrb string;
** DOES appear in the line (where_bdr ne 0);
if ln gt 23 and where_bdr ne 0 then do;
** for the output I tested (freq and print -- the RTF string;
** for a single line under the header started at position 31;
** but the line at the bottom of the table starts in position 2, so;
** you do NOT want that to change;
if where_bdr gt 2 and substr(_infile_,31,14)='\clbrdrb\brdrs' then do;
_infile_=substr(_infile_,1,30)||'\clbrdrb\brdrdb'||substr(_infile_,45);
** have increased the length of the _infile_ line by 1 character;
** so increase the length variable by 1;
ln+1;
end;
end;
put _infile_;
run;

** Now, check the log and assuming no errors;
** open the RTF file with Word -- you will NOT see the file in;
** the SAS Results Window because it was created with a;
** DATA step program.;

[/pre]

Before I could write a successful program, I had to spend a LOT of time reviewing the FREQ.RTF file in Notepad trying to figure out WHICH "\brdrs" needed to change since I have multiple single lines in the RTF file.

Good luck with your efforts!
cynthia
N/A
Posts: 0

Re: ODS RTF: How can I indicate SAS to merge specified cells of a table?

Posted in reply to Cynthia_sas
Many thanks for these informations!!
Contributor
Posts: 30

Re: ODS RTF: How can I indicate SAS to merge specified cells of a table?

Posted in reply to deleted_user
Hi Violaine, Cynthia

What you can do is tag a dummy value to the cell value to want to merge
then go through the pocress as Cynthia outlined.

When I first started out doing post-processing I have similar view to some of the things mentioned by Cynthia but not any more. Corupting file is frustrating but as I understand
RTF more and more I am able to resovle this more quickly and have less and less
occurance. Now I consider coruption as an advantage because this in a way is
a validation check on your post-processing.


Also I believe that the more you understand RTF the better tagsets you will be able to write when this is available in 9.2


Duong
N/A
Posts: 0

Re: ODS RTF: How can I indicate SAS to merge specified cells of a table?

I have found the following RTF control words: \clmgf and \clmrg. Perhaps, I could use this control words. I have tried the following program:

Data test;
Input a $ b $;
Cards;
1 2
3 4
5 6
;
Run;

Data test;
Set test;
c="^S={protectspecialchars=off pretext='\clmgf\cellx3'}"||a;
d="^S={protectspecialchars=off pretext='\clmrg\cellx4'}"||b;
Run;

ods rtf file="C:\temp.rtf";
proc print label noobs data=test; Run;
ods rtf close;
Ask a Question
Discussion stats
  • 10 replies
  • 675 views
  • 0 likes
  • 3 in conversation