BookmarkSubscribeRSS Feed
Walternate
Obsidian | Level 7

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!

7 REPLIES 7
AncaTilea
Pyrite | Level 9

Hi.

Would it be possible to post a small sample data?

Anca.

Walternate
Obsidian | Level 7

My raw data or the Excel spreadsheet?

AncaTilea
Pyrite | Level 9

Sample raw data.

Walternate
Obsidian | Level 7

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
Pyrite | Level 9

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
Super User

For 1, check the skip_space options in ODS Tagsets

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1393 views
  • 0 likes
  • 3 in conversation