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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.