Position of Column heading in ODS RTF using Tabulate

[lhatbrn]

When I use Tabulate to create a two-dimensional table with categorical variables by count and percent, the heading for the first variable (the leftmost column) is placed below the box (left and topmost cell of the table). The first category's label is placed in the row below that. The counts and percents associated with the first category, however, are in the row with the header, not the row with the category label. Furthermore, the journals we submit to most often, would prefer to have the column header in the box. Is there a way to get the column header into the box and the data for the first category into the row with that category's label using Tabulate? I don't want to have to use Report for this.

[Cynthia_sas]

Hi:
You are not the first person to note that behavior of PROC TABULATE. In fact, it was a topic of discussion back in October:
http://support.sas.com/forums/thread.jspa?threadID=11609&tstart=0
http://blogs.sas.com/sasdummy/index.php?/archives/212-PROC-REPORT-versus-TABULATE-two-SAS-heavyweigh...


Here's a LISTING output view of a simple TABULATE table.
[pre]
upper left is +------------------------------------------------+
BOX --------> | | N | PctN |
+----------------------+------------+------------+
|Age | | |
+----------------------+ | |
|11 | 2.00| 10.53|
+----------------------+------------+------------+
|12 | 5.00| 26.32|
+----------------------+------------+------------+
|13 | 3.00| 15.79|
+----------------------+------------+------------+
|14 | 4.00| 21.05|
+----------------------+------------+------------+
|15 | 4.00| 21.05|
+----------------------+------------+------------+
|16 | 1.00| 5.26|
+----------------------+------------+------------+
|All | 19.00| 100.00|
+------------------------------------------------+

^
|
This would be the first "column", column A in
Excel, but it is the "row header" area for PROC TABULATE.
[/pre]

As far as TABULATE is concerned, the area reserved for the CLASS variable AGE and the values of the AGE variable is not a "column" in the Excel sense of a spreadsheet starting with column A at the leftmost side of the sheet. For TABULATE, that area is called the "ROW Header" or ROW TITLE" area and it is controlled (width-wise) in the LISTING destination by the RTS option.

If all you have is 1 variable in the ROW HEADER or ROW TITLE area, it is very possible to "flatten" the headers, so that AGE, for example, is placed in the empty box area up on the same level with the header for N and the header for PCTN. The comma in the TABULATE TABLE statement tells TABULATE that AGE is in the ROW dimension and that N and PCTN are in the COLUMN dimension. From that standpoint, TABULATE is different from Excel, because TABULATE reserves that upper left area in order to show you the COLUMN headers on a level separate from the ROW area headers. Nice if that's OK with you, but frustrating if you expect TABULATE to act like Excel.

This is one of the reasons that my students switch to PROC REPORT. Generally because they have 2 nested variables in the ROW dimension and with 2 variables, while you can put the variables in the BOX area, you cannot "subdivide" the BOX area.

Consider the following programs. The issue you bring up is illustrated in Report #1 -- with only 1 CLASS variable, the "fix" is to use BOX=, as shown in Report #2. As an alternative, Report #3 shows the PROC REPORT approach. Then Report #4 shows the PROC REPORT alternative with 2 categories. Report #5 shows that the BOX area can only receive the label of 1 (and only 1 variable) -- otherwise, you have to use a string to put the 2 variable labels in the BOX area -- and you cannot subdivide the box area in the upper left -- so you can see that if you have 2 or 3 or more nested categories in the row dimension with TABULATE, the BOX= approach won't work the way you want -- while with PROC REPORT -- the "flattened" nature of the headers does exactly what you want.

cynthia
[pre]
ods listing close;
ods html file='c:\temp\tab_box.html' style=sasweb;
proc tabulate data=sashelp.class;
title '1) AGE Header below BOX';
class age;
table age all,
n pctn;
run;

** Can only put one variable label in the BOX;
proc tabulate data=sashelp.class;
title '2) AGE Header inside BOX';
class age;
table age=' ' all,
n pctn / box=AGE;
run;

proc report data=sashelp.class nowd;
title '3) Proc REPORT alternative';
column age n pctn;
define age / group style(column)=Header;
define n / 'N';
define pctn / 'PctN' f=percent9.2;
rbreak after / summarize style=Header;
run;

proc report data=sashelp.class nowd spanrows;
title '4) Proc REPORT alternative with 2 categories';
column age sex n pctn;
define age / group style(column)=Header;
define sex / group style(column)=Header;
define n / 'N';
define pctn / 'PctN' f=percent9.2;
rbreak after / summarize style=Header;
run;

proc tabulate data=sashelp.class;
title '5) TABULATE with Two nested CLASS variables';
class age sex;
table age=' '*sex=' ' all,
n pctn / box='Age and Sex';
run;

ods _all_ close;
[/pre]

[Ksharp]

Hi.
How about it:

[pre]
ods html file='c:\temp\x.html' style=meadow;
proc tabulate data=sashelp.class ;
class sex age;
table sex='',age*n='' /box='Sex';
run;
ods html close;
[/pre]



Ksharp

[Cynthia_sas]

Hi:
That changes the structure of the table -- by moving AGE to the COLUMN dimension and leaving SEX as the only variable in the ROW dimension. It is essentially the same as my example in REPORT #2 -- which will always work if there is only 1 variable in the ROW dimension.

So, yes, if it is acceptable to move AGE to the COLUMN dimension, then this is a possible solution. However, IF more than one variable is desired in the ROW dimension, and if it is NOT acceptable to move the second variable to the COLUMN dimension, then PROC REPORT is probably the better solution.

cynthia

[Ksharp]

Hi.
Cynthia.
I did not notice the post you have .See the time-stamp on both post.
You and Me is almost post at the same time.
Of course, Your explain is more details.And use proc report is maybe the better idea.


Ksharp