OK, I've been playing around with trying to output a report to Excel using ODS Tagsets. I found this Tagset on the SAS Support site:-
which didn't work right off, I had to change references to "colwidth" (but not "$colwidth") to "width", and it's producing Excel output quite nicely. I've added in a style sheet to set up colours and stuff, and used options to set up various items (column width is a little problematic, I think the amendment I made wasn't correct, though it did at least allow me to run the template).
All good so far. The only problem I'm still having is setting up the wrapping on the cells.
Anyone got any ideas?
Here's the style template:-
And here's the test code I'm using to view the results:-
The ODS PATH statement is so that SAS writes the templates to my work library rather than my user library, it means that changes made aren't persistent betwen sessions, which means I don't have to worry about screwing up my user settings.
If anyone has played with this stuff and worked it out, or has time to play with it and get some results, please let me know.
If not, at lteast it's a jumping point for you guys to have some fun with ODS.
which didn't work right off, I had to change references to "colwidth" (but not "$colwidth") to "width", and it's producing Excel output quite nicely. I've added in a style sheet to set up colours and stuff, and used options to set up various items (column width is a little problematic, I think the amendment I made wasn't correct, though it did at least allow me to run the template).
All good so far. The only problem I'm still having is setting up the wrapping on the cells.
Anyone got any ideas?
Here's the style template:-
Code:
ods path work.templat(update) sashelp.tmplmst(read) ;
proc template;
define style Styles.VwaExcel;
parent = styles.default;
style SystemTitle from SystemTitle /
background = #FFFFFF
foreground = #000000
font_style = Roman
font_weight = Bold
font_size = 4
font_face = "Arial, Helvetica, Sans Serif";
style SystemFooter from SystemFooter /
background = #FFFFFF
foreground = #000000
font_style = Italic
font_weight = Bold
font_size = 3
font_face = "Arial, Helvetica, Sans Serif";
style SysTitleAndFooterContainer from SysTitleAndFooterContainer /
borderwidth = 0
background = #FFFFFF
foreground = #FFFFFF
font_style = Italic
font_weight = Bold
font_size = 3
font_face = "Arial, Helvetica, Sans Serif";
style Body from Body /
rightmargin = 8px
leftmargin = 8px
background = #FFFFFF
foreground = #000000
font_style = Roman
font_weight = Medium
font_size = 2
font = ("Arial, Helvetica, Sans Serif");
style RowHeader from RowHeader /
background = #555555
foreground = #FFFFFF
font_style = Roman
font_weight = Bold
font_size = 2
font = ("Arial, Helvetica, Sans Serif");
style Header from Header /
background = #555555
bordercolor = #000000
borderwidth = 1px
foreground = #FFFFFF
font_style = Roman
font_weight = Bold
font_size = 2
font = ("Arial, Helvetica, Sans Serif");
style Data from Data /
borderwidth = 1px
bordercolor = #000000
background = #FFFFFF
foreground = #000000
font_style = Roman
font_weight = Medium
font_size = 2
font = ("Arial, Helvetica, Sans Serif");
style Table from Table /
cellspacing = 1px
bordercolor = #000000
borderwidth = 2px
background = #FFFFFF
foreground = #000000
font_style = Roman
font_weight = Medium
font_size = 2
font = ("Arial, Helvetica, Sans Serif");
end;
run;
And here's the test code I'm using to view the results:-
Code:
ods path work.templat(update) sashelp.tmplmst(read) ;
ods listing close;
*ods tagsets.ExcelXP file="C:\test\daily_rep.xls";
ods tagsets.ExcelXP file="/apps/dw_house/reports/out/srm/chris/daily_rep2.xls"
options(default_column_width='5,20,30'
row_repeat='header'
frozen_headers='yes')
style=styles.vwaExcel;
*ods html file="C:\test\daily_rep.htm";
title 'Test Report';
title2 'Testing new Tagset';
title3 "EXCELXP";
proc report data=SASHELP.EISMSG
nowd split='*';
columns MSGID
MNEMONIC
TEXT
;
define MSGID /order 'Message*ID';
define MNEMONIC /display 'Mnemonic' ;
define TEXT /display 'Text*Of*Message' ;
run;
*ods html close;
ods tagsets.ExcelXP close;
ods listing;
The ODS PATH statement is so that SAS writes the templates to my work library rather than my user library, it means that changes made aren't persistent betwen sessions, which means I don't have to worry about screwing up my user settings.
If anyone has played with this stuff and worked it out, or has time to play with it and get some results, please let me know.
If not, at lteast it's a jumping point for you guys to have some fun with ODS.