Achieving complex headers in Excelxp tagsets proc report

[SamA]

Hi,

I'm using Sas 9.1.3, with Excelxp tagsets from 2005 (maybe I need a new version).  I have a dataset generated by proc summary that I am using wiht proc report to output to Excel.  Thep problem I have is controlling the alingnment of the header boxes. Attached is a one page pdf displaying a sample desired output.  For instance, I would like the "family characteristic" header to be one merged box with the text centered, and then have the "percentage of families saved" and "percentage of familes" headers reach all the way to the year spanned header.  Currently the way I do it has two blank cells at the top of the cell since there are two spannings (the year and the 'income' header above the mean and median); also since I set 'income' to span there is a blank cell at the top of the percentage of families header cells.

In the table below (illustrating my output) the two cells under 1998 would actually be merged in my output and contain the word 'income' I just can't get it to do this in the table in this discussion.

Header 1	1998
Family characteristic	Median	Mean

Here is the column statement I am using:

COLUMN LABEL YEAR, (('Income' INCOME_MEAN INCOME_MEDIAN)   PERCENT_SAVING  PCT_FAMILIES);

where label is a character variable label for each row, whose header title is "family characteristic".  I have searched a lot online and can't find anywhere where it mentions something like this.  I have also not seen examples of similar output in papers.  The closest I can find is where you can 'erase' the gridlines by making them white using DDE but this wouldn't help me center the header title within the 'merged' cell, also I am using linux which appears not to work with DDE, and I think these features are new to SAS 9.2.  Any tips?  Or am I just wasting my time trying to do this?

[Cynthia_sas]

Hi,

Your example actually looks like a fairly standard PROC TABULATE output...customized with some extra ODS to get the dots and the italics, etc into the output.

The big "box" area in TABULATE is used to show the "intersection" of the row dimension and the column dimensions of the table.

I do not believe you fake out a box like that with PROC REPORT. In fact, people use PROC REPORT a lot of times because they do NOT want that box area that they get with TABULATE.

cynthia

[SamA]

Hi Cynthia,

Thanks for the advice.  The thing is that I need to do some post output processing to the output summary dataset (to censor low N values, for example).  I tried with proc summary to output a dataset and i seem to get the same thing I would get from the proc summary steps I am using.  It seems that this need for post-output processing would make invalidate the use of proc tabulate.  Perhaps I misunderstand though.  I tried tabulate and the printed table looks pretty good though.