BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
10 REPLIES 10
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
Many thanks cynthia, I will try to contact Tech Support!

Violaine
Duong
Obsidian | Level 7
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
deleted_user
Not applicable
How can I do that? I am very interested!

Violaine
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
Many thanks for these informations!!
Duong
Obsidian | Level 7
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
deleted_user
Not applicable
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1868 views
  • 0 likes
  • 3 in conversation