excelxp: spanned/not spanned header borders

[staffordst]

The table headers produced by either of the following reports are the same, and are okay, except for the border appearance requirement as shown in the attachment. A 'verical merge' of the headers rows is possible by using DDE to set the borders, but unfortunately the production system will be on a platform where using DDE is not possible. ods tagsets.excelxp file='I:\dev\helpdesk\V2\test1.xls'; proc report data=t1 nowd; columns ('header one' v1 v2) v3 v4 v5; DEFINE v1 /display; DEFINE v2 /display; DEFINE v3 /display; DEFINE v4 /display; DEFINE v5 /display; run; ods tagsets.excelxp close; ods tagsets.excelxp file='I:\dev\helpdesk\V2\test2.xls'; proc report data=t1 nowd; columns ('header one' v1 v2) (' ' v3 v4 v5); DEFINE v1 /display; DEFINE v2 /display; DEFINE v3 /display; DEFINE v4 /display; DEFINE v5 /display; run; ods tagsets.excelxp close;

[staffordst]

Sorry I messed up the post format. Here is the code:

ods tagsets.excelxp file='I:\dev\helpdesk\Ackerman\V2\test1.xls';  

proc report data=t1 nowd; columns ('header one' v1 v2) v3 v4 v5;

DEFINE v1 /display;

DEFINE v2 /display;

DEFINE v3 /display;

DEFINE v4 /display;

DEFINE v5 /display;

run;

ods tagsets.excelxp close;

ods tagsets.excelxp file='I:\dev\helpdesk\Ackerman\V2\test2.xls';

proc report data=t1 nowd; columns ('header one' v1 v2) (' ' v3 v4 v5);

DEFINE v1 /display;

DEFINE v2 /display;

DEFINE v3 /display;

DEFINE v4 /display;

DEFINE v5 /display;

run;

ods tagsets.excelxp close;

[Cynthia_sas]

Hi:

The type of headers you want are not generally possible using PROC REPORT. PROC REPORT "wants" the rows for spanning headers to span the whole table. So the empty header above v3, v4 and v5 cannot be "disappeared" or merged with PROC REPORT. That's why your two code snippets produce the same results. With your #1, REPORT says "hey, there's some text above V1 and V2, oh, that's OK, let me help you by putting a big empty cell above v3, v4 and v5" -- PROC REPORT is very helpful! With your #2 code, you are accomplishing exactly the same thing, except you've explicitly told REPORT to put blanks above the other 3 variables.

The code below shows some different header appearances and behavior with the different procedures. I don't know what you have in your T1 data set, so I just used SASHELP.CLASS for my code.

cynthia

ods tagsets.excelxp file='c:\temp\spanning.xml' style=htmlblue;

ods html file='c:\temp\spanning.html' style=htmlblue;

proc tabulate data=sashelp.class;

  title 'With PROC TABULATE';

  class sex age;

  var height;

  table sex*age,

        height=' '*(min mean max)/box='Header One';

run;

proc report data=sashelp.class nowd;

  title 'With PROC REPORT';

  column ("Header One" sex age) height,(min mean max);

  define sex / group;

  define age / group;

  define height / analysis ' ';

  define min / f=comma6.2;

  define mean / f=comma6.2;

  define max / f=comma6.2;

run;

proc report data=sashelp.class nowd

  style(header)={just=l};

  title 'Alternate PROC REPORT';

  column ("Header One" sex age  height,(min mean max));

  define sex / group style(header)={just=c};

  define age / group style(header)={just=c};

  define height / analysis ' ';

  define min / f=comma6.2  style(header)={just=c};

  define mean / f=comma6.2  style(header)={just=c};

  define max / f=comma6.2  style(header)={just=c};

run;

ods _all_ close;