Excel Tagsets: Can I left justify the header row?

[ScottBass]

Sample code:

%let excel_xml=c:\temp\excel_tagsets.xml;

ods _all_ close;

ods noproctitle noresults;

ods tagsets.excelxp

  file="&excel_xml"

  style=statistical

  options(

auto_subtotals='yes'

frozen_headers='yes'

sheet_interval='none'

autofilter='all'

  );

%macro code(word);

ods tagsets.excelxp options(sheet_interval='none' sheet_name="&word");

proc print data=sashelp.&word noobs;

run;

%mend;

%code(class)

%code(cars)

%code(fish)

%code(shoes)

%code(stocks)

ods _all_ close;

ods listing;

I also reference this doc hit:  Base SAS: Quick Reference for TAGSETS.EXCELXP Tagset

Is there a way I can:

1) Left justify all the column headers (row 1), including numeric columns?

2) Autofit the columns, including the headers and the autofilter dropdown arrow?

IOW, I want SAS to create the equivalent of me doing in Excel:

* left justify row 1

* select all cells (click the upper left "cell")

* double click a column border

For all worksheets in the sample output.

If it's in the doc hit above I'm missing it.

Thanks,

Scott

[Cynthia_sas]

Hi:

  This works for me to left justify the headers. Also, when you use sheet_interval='none', I think that the autofilter only applies to the first row in the sheet -- which may not be what you want, depending on how your other tables are structured.

  I do not believe there is a way to autofit the column widths. There are 2 ways that I know of to impact the column widths and both are trial and error and both have been illustrated in the forum with examples:

1) using the CELLWIDTH= option in a STYLE override for the column (would put this on each individual column) -- you'd need some idea of the width you want for each column

OR

2) using the sub-option ABSOLUTE_COLUMN_WIDTH -- but again, even though you only specify this in the ODS invocation statement, you still need some idea of the width you want for each column.

  Here's an example of a previous posting on the use of CELLWIDTH. (https://communities.sas.com/message/172228#172228) and here's one with the use of  ABSOLUTE_COLUMN_WIDTH= sub-option (look at the screen shot to see the code ) https://communities.sas.com/message/112209#112209.

cynthia

ods _all_ close;

ods tagsets.excelxp
  file="c:\temp\test_just.xml"
  style=statistical
  options( auto_subtotals='yes'
           frozen_headers='yes'
           sheet_interval='none'
           autofilter='all' );
   
proc print data=sashelp.class noobs
  style(header)={just=l};
run;
  
ods _all_ close;