topic Re: Title/header trouble in an Excel sheet in ODS and Base Reporting

Title/header trouble in an Excel sheet

Walternate — Thu, 29 Aug 2013 14:22:38 GMT

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!

Re: Title/header trouble in an Excel sheet

AncaTilea — Thu, 29 Aug 2013 14:46:09 GMT

Hi.

Would it be possible to post a small sample data?

Anca.

Re: Title/header trouble in an Excel sheet

Reeza — Thu, 29 Aug 2013 15:02:47 GMT

For 1, check the skip_space options in ODS Tagsets

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

Re: Title/header trouble in an Excel sheet

Walternate — Thu, 29 Aug 2013 15:09:53 GMT

My raw data or the Excel spreadsheet?

Re: Title/header trouble in an Excel sheet

AncaTilea — Thu, 29 Aug 2013 15:14:31 GMT

Sample raw data.

Re: Title/header trouble in an Excel sheet

Walternate — Thu, 29 Aug 2013 15:23:24 GMT

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

Re: Title/header trouble in an Excel sheet

AncaTilea — Thu, 29 Aug 2013 15:43:31 GMT

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.

Re: Title/header trouble in an Excel sheet

Reeza — Thu, 29 Aug 2013 16:54:15 GMT

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}";