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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.