topic excelxp: spanned/not spanned header borders in ODS and Base Reporting

excelxp: spanned/not spanned header borders

staffordst — Wed, 11 Jan 2012 20:13:05 GMT

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;

excelxp: spanned/not spanned header borders

staffordst — Wed, 11 Jan 2012 20:24:44 GMT

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;

Re: excelxp: spanned/not spanned header borders

Cynthia_sas — Wed, 11 Jan 2012 21:16:40 GMT

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;