Excel column formatting using ODS tagsets.msoffice2k_x and proc tabulate

[Mgarret]

Hi--

I'm having issues with specifying column width in excel when using ODS  tagsets.msoffice2k_x and proc tabulate. Below I have two proc tabulate statements. The first only statement generates one question frequency table and the next statement generates several. This is done so titles can be placed in the appropriate place in the spreadsheet.  The column widths I’ve defined are not generating correctly for all tables (the heights seem to be). When I run the program for just the first proc tabulate statement (so close ODS after the first table) the column widths work but if I output multiple tables the column widths do not hold even though they are written into the code for each table – the tables end up being very wide. I’m trying to format the spreadsheet so all the tables fit and the excel file prints appropriately.

Any help is aperciated--

data test;

  infile datalines dlm=',' dsd;

  input Program_Name $ question response;

return;

datalines;

"Program1 ",1,1

"Program1 ",1,0

"Program1 ",1,1

"Program1 ",1,0

"Program1 ",1,1

"Program1 ",1,1

"Program1 ",1,1

"Program1 ",1,0

"Program1 ",1,1

"Program1 ",1,0

"Program1 ",1,1

"Program1 ",1,1

"Program1 ",1,1

" Program2 ",1,1

" Program2 ",1,1

" Program2 ",1,0

" Program2 ",1,1

" Program2 ",1,1

" Program2 ",1,0

" Program2 ",1,1;

Run;

ods listing close;

  ods tagsets.msoffice2k_x file="Z:\SAS\SASTESTS\test.xls"

      options(

               scale="100"

               margin=".1in .1in .1in .1in"

               print_header_margin=".2in"

               print_footer_margin=".2in"

               orientation="portrait"

               papersize="legal"

ods escapechar='^';

proc tabulate data=test

(where=(question in('Q16a')))

style=[background=white foreground=black  font_face='Arial Narrow' font_size=10.5pt just=c cellwidth=.6in];

title1 "^S={font_face='Arial Narrow' font_weight=bold font_size=12pt}

TITLE";

title2 "^S={font_face='Arial Narrow' font_weight=bold font_size=12pt}

TITLE";

class question program_name

/ style={font_face='Arial Narrow' font_weight=bold font_size=11pt cellheight=50 };

class Responce / order=formatted

style={font_face='Arial Narrow' font_weight=bold font_size=11pt cellwidth=.6in};

classlev question

/style=[font_face='Arial Narrow' font_size=11pt];

classlev  program_name

/style={font_face='Arial Narrow' font_size=10pt cellwidth=.6in cellheight=.4in just=l};

classlev

Responce

/style={font_face='Arial Narrow' font_size=11pt  };

table Question=' ',

program_name =' '

all='Overall Agency Totals',

Responce=' '*(n='Count'*f=best8. pctn='%'*f=p.)

n='Count Total' pctn='% Total' *f=p.

/style={font_face='Arial Narrow' font_size=11pt cellwidth=.6in}

row=float 

box={label='Count^{newline 1}Overall^_%'

style={font_face='Arial Narrow' font_size=10pt font_weight=bold cellwidth=.6in}};

format question $ques.;

format responce $resp.;

keyword n pctn  /

style=[font_weight=bold foreground=black  font_face='Arial Narrow' font_size=10pt just=c];

keyword all/ style=[font_weight=bold foreground=black  font_face='Arial Narrow' font_size=10pt just=l];

title;

run;

/*when I close ODS the column formatting does not hold*/

proc tabulate data=want

(where=(question in('Q16b',

'Q17','Q18','Q19','Q22','Q23',

'Q23','Q23a','Q24','Q25','Q26')))

style=[background=white foreground=black  font_face='Arial Narrow' font_size=10.5pt just=c];

class question program_name

/ style={font_face='Arial Narrow' font_weight=bold font_size=11pt cellheight=50 };

class Responce / order=formatted

style={font_face='Arial Narrow' font_weight=bold font_size=11pt};

classlev question

/style=[font_face='Arial Narrow' font_size=11pt];

classlev  program_name

/style={font_face='Arial Narrow' font_size=10pt cellwidth=.6in cellheight=.4in just=l};

classlev

Responce

/style={font_face='Arial Narrow' font_size=11pt  };

table Question=' ',

program_name =' '

all='Overall Agency Totals',

Responce1=' '*(n='Count'*f=best8. pctn='%'*f=p.)

n='Count Total' pctn='% Total' *f=p.

/style={font_face='Arial Narrow' font_size=11pt cellwidth=.6in }

row=float 

box={label='Count^{newline 1}Overall^_%'

style={font_face='Arial Narrow' font_size=10pt font_weight=bold cellwidth=.6in}};

format question $ques.;

format responce1 $resp.;

keyword n pctn  /

style=[font_weight=bold foreground=black  font_face='Arial Narrow' font_size=10pt just=c];

keyword all/ style=[font_weight=bold foreground=black  font_face='Arial Narrow' font_size=10pt just=l];

run;

/*when I close ODS the column formatting does not hold*/

ods listing;

ods _all_ close;

[Cynthia_sas]

Hi:

  The issue is NOT SAS, but is Excel and how Excel renders the HTML file that you create with ODS TAGSETS.MSOFFICE2K_X. No matter what cellwidth you specify, Excel might not respect the cellwidth. It has defaults for column type and column width and frequently does whatever it wants and not what you specify in SAS.

  The "fix" is to use the HTMLSTYLE attribute to send a Microsoft format from SAS to Excel. I'm not on a computer with fast Internet connections, so I will just refer you to the support.sas.com web site and search for HTMLSTYLE and mso-number-format. Or, search for my SGF paper "Don't Gamble with Your Formats" -- from SGF 2011. I had an example of setting width with HTMLSTYLE in the paper.

cynthia