Title/header trouble in an Excel sheet

[Walternate]

Hello,

I am trying to use ODS to create a formatted table in Excel via SAS. I'm actually most of the way there, but I was hoping to get help with a few more steps I'd like to take to perfect it.

My questions are:

1. Is there a way to eliminate the blank row automatically created between the title and the column headers?

2. Why isn't my Team column header centered (see code below)?

3. Is there a way to do a middle alignment in Excel via SAS (ie, have my text vertically centered in the Excel cell)?

This is the code I'm using:

ods _all_ close ;

ods tagsets.ExcelXP path='c:\excel sheets'

file="TotalWins.xml"

style=listing;

title font=trebuchet  bold bcolor=grey justify=center

'Wins by Team';

ods tagsets.Excelxp options(embedded_titles='yes' embedded_footnotes='yes'

print_header='&C&A');

ods tagsets.excelxp options(sheet_name='Wins by Team');

proc print data=Winsbyteam noobs style(header)=[background=silver

font_size=10pt just=center font_face=Trebuchet font_weight=bold];

var Team/style(column)=[font_face=trebuchet font_size=8pt] ;

var winrec/style(column)=[just=center font_face=trebuchet font_size=8pt];

run;quit;

ods tagsets.ExcelXP close;

run;

Any help is much appreciated. Thanks!

[AncaTilea]

Hi.

Would it be possible to post a small sample data?

Anca.

[Walternate]

My raw data or the Excel spreadsheet?

[AncaTilea]

Sample raw data.

[Walternate]

Sure.

It's just two columns with a list of Little League Teams and a count of how many times that team has won.

So:

Team              Wins

Tigers              10

Jackals             5

Comets             7

Marlins            12

Thunder           15

etc

[AncaTilea]

Hi.

As correctly suggested, you need to use the skip_space option:

...

ods _all_ close ;

ods tagsets.ExcelXP path='&path.\' file="TotalWins.xml"

style=listing options(SKIP_SPACE='0,0,0,1,0,0,0');

....

Good luck!

Anca.

[Reeza]

For 1, check the skip_space options in ODS Tagsets

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

[Reeza]

Re #3. The link I sent also seems to suggest that center in proc report can be used to center variables if required.

See the example under:

Default Column Width and Width Fudge

I usually use something like the following for titles, but not sure it works in tagsets:

ods eschapechar="^";

title "^{style [color=Black fontsize=14pt font_face=georgia just=center] Teams by Win}";