Excel Tagsets: Can I left justify the header row?

ScottBass — Mon, 04 Nov 2013 00:49:41 GMT

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

Re: Excel Tagsets: Can I left justify the header row?

Cynthia_sas — Mon, 04 Nov 2013 01:35:30 GMT

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

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;

topic Re: Excel Tagsets: Can I left justify the header row? in ODS and Base Reporting

Excel Tagsets: Can I left justify the header row?

Re: Excel Tagsets: Can I left justify the header row?