The SAS Output Delivery System and reporting techniques

PROC Report RTF Headers of different cell height

Reply
N/A
Posts: 0

PROC Report RTF Headers of different cell height

I am looking for an rtf ODS output with proc report and the final table should look line this


xxxx --------------------------(Supposed to be solidline)
xxx xxx xxxx x y z xx yy

The dotted line should allign just on top of the coulmn names (single row column label) but with less space between the column labels and dotted line.

Here is the syntax which i have used, the problem with my syntax output is that
the dotted line is getting displayed on top of the column labels but there is lot of space or width between the dotted line and column lables.

proc report NOWD CENTER MISSING
style(column)={font_face= "Times New Roman" font_size=9pt asis=on background=_undef_ font_weight= medium }
style(report)={cellheight=.25in cellpadding=0pt rules=group frame=void background=_undef_}
style(header)={BOTTOMMARGIN = 0IN font_face= "Times New Roman" font_size=9pt background=_undef_ font_weight= medium just=c}
data =&var.t
ps=80 ls=160
split = "|"
nowindows;
columns PAGEIT COHORT USUBJID DATE LP ("^R/RTF'\brdrb\brdrs\brdrw1'&var2|" &num) ;
define PAGEIT /ORDER NOPRINT ;
define COHORT/ORDER style(column)=[cellwidth=5% ]style(header)=[cellheight=0.10IN]center "xxxx ";
define USUBJID/display style(column)=[cellwidth=5% ]style(header)=[cellheight=0.10IN]center "xxxxx";
define DATE/display style(column)=[cellwidth=7% ]style(header)=[cellheight=0.45IN]center "yyyyyyy";
define LP/display style(column)=[cellwidth=5% ]style(header)=[cellheight=0.45IN]center "zzzzzzzz";
%macro define (var,var1);
define &var/display style(column)=[cellwidth=3% ]STYLE(HEADER)=[CELLHEIGHT=0.8IN]center &var1;
%mend;
%define (_1,'1.');
%define (_2,'2.');
%define (_3,'3.');
%define (_4,'4.');
%define (_5,'5.');
%define (_6,'6.');
%define (_7,'7.');
%define (_8,'8.');
%define (_9,'9.');
%define (_10,'10.');
%define (_11,'11.');
%define (_12,'12.');
%define (_13,'13.');
%define (_14,'14.');
%define (_15,'15.');
%define (_16,'16.');
%define (_17,'17.');
%define (_18,'18.');
%define (_19,'19.');
%define (_20,'20.');
%define (_21,'21.');
%define (_22,'22.');
%define (_23,'23.');

break after pageit/page;

compute BEFORE cohort;
line ' ';
endcomp;

Thanks Message was edited by: Lucy
SAS Super FREQ
Posts: 8,740

Re: PROC Report RTF Headers of different cell height

Hi:
You are not showing your entire ODS RTF invocation (so we can see whether you have any relevant ODS RTF options or what ODS STYLE= option you are using. You show how &var and how &var1 get set, but you do not show how &var2 or &num are assigned values. (As an aside...personally, I would never put the macro definition (the %MACRO/%MEND --inside the PROC REPORT step -- conceptually, the definition of the macro program can (and probably would be better understood if it were) defined -outside- of the PROC REPORT step. You do not have to put your macro definition inside the PROC REPORT step in order to use it there. However, that is a minor issue compared to some of the other possible issues with your CELLHEIGHTS.)

You have hardcoded cellheights for every DEFINE statement for &VAR; you have a hardcoded cellheight for STYLE(REPORT); and hardcoded, but different cellheights for COHORT, USUBJID, DATE and LP. So it might be possible that you are specifying conflicting cellheights for a single report header area....at a guess. Generally speaking, SAS/PROC REPORT/ODS would want EVERY column on the same row to be the SAME cellheight. I would expect that the cellheight for your spanned header might be able to be a different height, but the basic cellheight for all headers that are in the same "row" would all need to be the same height. It is possible that ODS will not, in fact, give you an error message because it doesn't see anything wrong with differing cellheights. But it's also possible that either ODS or Word just "readjusts" the cellheights behind the scenes.

For example, if I use this code on SASHELP.CLASS, removing the macro code because it is an unnecessary layer of complexity:
[pre]
ods listing close;
ods rtf file='c:\temp\forum_ctrl_strings.rtf';
ods escapechar='^';

proc report NOWD CENTER MISSING
style(column)={font_face= "Times New Roman"
font_size=9pt asis=on background=_undef_
font_weight= medium }
style(report)={cellheight=.25in cellpadding=0pt
rules=group frame=void background=_undef_}
style(header)={BOTTOMMARGIN = 0IN font_face= "Times New Roman"
font_size=9pt background=_undef_
font_weight= medium just=c}
data =sashelp.class
split = "|"
nowindows;
columns sex name age
("^R/RTF'\brdrb\brdrs\brdrw1'something|" height weight height=ht2 weight=wt2) ;
define sex /ORDER NOPRINT ;
define name/ORDER style(column)=[cellwidth=5% ]
style(header)=[cellheight=0.10IN]center "xxxx ";
define age/display style(column)=[cellwidth=5% ]
style(header)=[cellheight=0.10IN]center "xxxxx";
define height/display style(column)=[cellwidth=3% ]
STYLE(HEADER)=[CELLHEIGHT=0.45IN]center '1.';
define weight/display style(column)=[cellwidth=3% ]
STYLE(HEADER)=[CELLHEIGHT=0.45IN]center '2.';
define ht2/display style(column)=[cellwidth=3% ]
STYLE(HEADER)=[CELLHEIGHT=0.45IN]center '3.';
define wt2/display style(column)=[cellwidth=3% ]
STYLE(HEADER)=[CELLHEIGHT=0.45IN]center '4.';

compute BEFORE sex;
line 'LINE before ';
endcomp;
run;
ods _all_ close;
[/pre]

And then, if I look at the generated RTF for the "row" with NAME, AGE, HEIGHT, WEIGHT, HT2 and WT2 -- inside Word, when the RTF file is rendered, the report row for those header cells is exactly .45" for every cell, in spite of the fact that NAME, and AGE had cellheights of .1" and the other variables all had cell heights of .45" -- clearly no matter what information ODS sent in the RTF control strings, the word processor (Word) either ignores the differing cellheights or uses the largest of the cellheights that were specified for that row.

In addition, you use some syntax that is odd, in the context of your statement that you are creating ODS RTF output -- for example, PS and LS have no impact on ODS RTF output -- they are LISTING only options.

For the RTF control string that you pass in as "raw" text, I do not understand how you would ever get a dotted line from this: \brdrb\brdrs\brdrw1 control string. As far as I know, those RTF control strings translate to:
\brdrb (set border bottom)
\brdrs (to a single thickness border line)
\brdrw1 (to a width of 1TWIP ...as explained in the spec that normally, you specify brdrwn where N is the width in twips of the pen used to draw the paragraph border line. N cannot be greater than 75. To obtain a larger border width, the \brdth control word can be used to obtain a width double that of N . )

Other possibilities for line style (according to the RTF spec are):
\brdrth Double-thickness border.
\brdrsh Shadowed border.
\brdrdb Double border.
\brdrdot Dotted border.
\brdrdash Dashed border.
\brdrhair Hairline border.
\brdrdashsm Dash border (small).
\brdrdashd Dot dash border.
\brdrdashdd Dot dot dash border.
\brdrtriple Triple border.
\brdrtnthsg Thick thin border (small).
\brdrthtnsg Thin thick border (small).
\brdrtnthtnsg Thin thick thin border (small).
\brdrtnthmg Thick thin border (medium).
from--http://www.biblioscape.com/rtf15_spec.htm

So I would expect you to use a different control string if you wanted a dotted line instead of a single-thickness (or solid) line.

Without seeing all your code and understanding a bit more about why you want to set different cellheights, it's not possible to comment any more. Perhaps these comments will help you a bit. Or you might consider working with Tech Support -- since you could show them ALL your code, and ALL your data and they could make specific suggestions about how to get closer to what you want.

cynthia
N/A
Posts: 0

Re: PROC Report RTF Headers of different cell height

Hi,

Thanks for the comments, Let me us use the syntax created by you to explain my situation of why i need different cell height.
When you run the SASHELP.CLASS proc report, in the header i want the 'Something' followed by the solid line to be just on top of the columns 1, 2,.3, 4 by getting rid of the space in between. For my report purpose one of the column header ocuppies two rows instead of one.

To make it better understand my situation i have modified one of the column definition into this.

define age/display style(column)=[cellwidth=5% ] style(header)=[cellheight=0.10IN]center "xxxxxxxxxxxxxx";

Now when you run the proc reporet with this modification you can see that the second column label ocuppies two rows.

What i am looking for is that the 'Something' header should line up with the bottom row of the column label "xxxxxxxxxxxxxx", instead of being on the top of the entire column headers.


Thanks for your precious time and input into this.
SAS Super FREQ
Posts: 8,740

Re: PROC Report RTF Headers of different cell height

Hi:
I'm still confused. I don't know what you mean when you say:
" 'Something' header should line up with the bottom row of the column label "xxxxxxxxxxxxxx" -- that's just not how spanning headers work -- if I understand correctly what you want. A SPANNING header always goes ABOVE the headers for the columns that it spans -- it would never go UNDER or on the same level as the columns that it spans.

If I simplify the code a lot and take out the RAW text and all the CELLHEIGHTS and CELLWIDTHS, I can get a more streamlined look and feel to the RTF output, but the string 'something' still sits ABOVE the 4 columns that it spans.

You might want to work with Tech Support on what you want. I am having a hard time visualizing it and I'm not sure that what you want to do can be achieved. If you go into Word and make the column headers that you want (outside of SAS), you could send that document to Tech Support and then ask them whether it is possible to replicate that look and feel with ODS.

cynthia

[pre]
ods listing close;
title; footnote;
ods rtf file='c:\temp\forum_ctrl_strings_simple.rtf'
style=journal;

proc report NOWD data =sashelp.class nowindows
CENTER MISSING
style(column)={font_face= "Times New Roman"
font_size=9pt
font_weight= medium }

style(header)={font_face= "Times New Roman"
protectspecialchars=off
font_size=9pt
font_weight= medium just=c};

columns sex name age
("\brdrb\brdrs\brdrw1 {something}" height weight height=ht2 weight=wt2) ;

define sex /ORDER NOPRINT ;
define name/ORDER "xxxxx";
define age/display "xxxxx";
define height/display center '1.';
define weight/display center '2.';
define ht2/display center '3.';
define wt2/display center '4.';
run;
ods _all_ close;
[/pre]
N/A
Posts: 0

Re: PROC Report RTF Headers of different cell height

Thanks for the reply.

I want 'Something' to span above 1, 2, 3 and 4 and should be displayed above the spanned columns. Only difference is that I want it to be displayed above the spanned columns avoiding space between the span text and spanned columns. Especially in this case when the column age has more characters.
Please try to define age or sex with more 'x' characters, may be like 'xxxxxxxxxxx' and you can see that there is space between the span string ' Something' and the columns which it spans.
I want to have the string to be displayed just above the spanned columns with no space in between.
SAS Super FREQ
Posts: 8,740

Re: PROC Report RTF Headers of different cell height

Hi:
If I do this:
[pre]
. . . same code as previously posted . . .
columns sex name age
("\brdrb\brdrs\brdrw1 {something}" height weight height=ht2 weight=wt2) ;

define sex /ORDER NOPRINT ;
define name/ORDER "xxxxx yyyyyyyy zzzzzzz";
define age/display "xxxxx yyyyyyy zzzzzzz";
. . . same code etc. . .
[/pre]

Then you are right that Word autoadjusts the cell for NAME and the cell for AGE to be higher (because the header wraps). Conceptually, even though you are disappearing the interior table lines, Word is still getting a TABLE definition. In that TABLE definition, the headers are coming across as looking like this (drawn with -- and | and +):
[pre]
+-------------------+-------------------------------------------+
| | something |
|-------------------+-------------------------------------------|
| xxxxx | xxxxx | | | | |
| yyyyyyy | yyyyyyy | | | | |
| zzzzzzz | zzzzzzz | 1. | 2. | 3. | 4. |
|---------+---------+----------+----------+----------+----------|
|Alice | 13| 56.5| 84| 56.5| 84|
|---------+---------+----------+----------+----------+----------|
[/pre]

You can prove this to yourself by going into Word and formatting the table with the first choice to make all the border lines black around every cell under Table Style Options in Word (in Office 2007, the Menu is Design --> first table with all black lines).

As you can see in the "hand drawn" table above, the headers for NAME, AGE, HEIGHT, WEIGHT, HT2 and WT2 are on on the same "level" -- If the cells for NAME and AGE were wider (so that their headers did not need to be wrapped), then the WHOLE space on that level -- from end to end would be reduced. I have annotated the above conceptual header layout:
[pre]
+-------------------+-------------------------------------------+ 1) instructions for top of table
| | something | 2) header which spans last 4 cols, empty cell above cols 1 and 2
|-------------------+-------------------------------------------| 3) conceptual "end of row" for spanning header
| xxxxx | xxxxx | | | | |
| yyyyyyy | yyyyyyy | | | | | 4) This area is the "main" header area for the items on the
| zzzzzzz | zzzzzzz | 1. | 2. | 3. | 4. | column statement. Because 'xxxx yyyy zzz' header wraps that
|---------+---------+----------+----------+----------+----------| forces this row to be adjusted in height.
|Alice | 13| 56.5| 84| 56.5| 84| 5) first data row
|---------+---------+----------+----------+----------+----------|
[/pre]

At the point where the table section #4 is written, table sections 1 and 2 and 3 have already been written. The only thing you could do at this point is shift some text for NAME and AGE headers into the empty cell area on section #1. Something like this:
[pre]
columns sex ("xxxxx yyyyyyy" name) ("aaaaa bbbbbbb" age)
("\brdrb\brdrs\brdrw1 {something}" height weight height=ht2 weight=wt2) ;
define sex /ORDER NOPRINT ;
define name/ORDER "name";
[/pre]

Your COLUMN statement in PROC REPORT is telling PROC REPORT which columns go on each report ROW and which column comes first, which column comes second on the report row. Your TABLE, built by PROC REPORT is composed of those columns arranged in REPORT ROWS. Your individual columns have HEADER cells (where the column name or label will be placed) at the top of the table and DATA cells (where the data values will be placed) under the HEADER cells.

So, when you use PROC REPORT, you are not placing individually independent columns in a document -- you are instructing PROC REPORT to make a TABLE that is appropriate for the destination. And that TABLE is primarily composed of report ROWS and inside each report ROW you will have the same number of columns. As you can see above, when you use spanning headers, you can "disappear" the interior table dividers on the spanning row (you can disappear the | divider). But you can't shift the -- divider between each row by making one cell height for NAME and AGE and a different cellheight for the other 4 variables on the report row. The cellheight for table section #4 will be the same for every item on that report ROW.

Take SAS out of the picture and go into Word and make a table with 6 columns like this:
[pre]
+-------------------+-------------------------------------------+
| one | two | three | four | five | six |
+---------+---------+----------+----------+----------+----------+
[/pre]

Then, inside Word, insert a row above your first row. Your new row will now have 6 columns. Next, you will have to merge the first 2 columns in the new row in order to disappear the | divider and you will have to merge the next 4 columns in the new row in order to have an empty cell span the last 4 columns. Your new table should look like this (if you type text into the new row):
[pre]
+-------------------+-------------------------------------------+
| Span1 | Span2 |
+-------------------+-------------------------------------------+
| one | two | three | four | five | six |
+---------+---------+----------+----------+----------+----------+
[/pre]

Now, type some extra text into the cell for 'one' and as you type, you will discover that as soon as there is not enough room in the cell for 'one' for the text that you are typing, Word will increase the cellheight of the entire ROW to accomodate the text that you are entering into the first cell:
[pre]
+-------------------+-------------------------------------------+
| Span1 | Span2 |
+-------------------+-------------------------------------------+
| one | two | three | four | five | six |
| xxxxx | | | | | |
| yyyyy | | | | | |
+---------+---------+----------+----------+----------+----------+
[/pre]

Word will NOT allow you to make the cell for 'one' a different height than the cells on the rest of the row. Try it. If you grab the row divider line and try to shrink just the first cell, Word will not let you make that change. If you highlight the contents of that cell and right-mouse click and select Auto-Fit, you will see that the ONLY adjustments you can make to the cell are WIDTH adjustments -- you can Autofit to Contents, Autofit to Window or set a Fixed Column Width. If Microsoft Word will not allow you to make one cell a different height in a table, then there's nothing in ODS that you can do to make different cell heights on one row.

cynthia
Ask a Question
Discussion stats
  • 5 replies
  • 1517 views
  • 0 likes
  • 2 in conversation