07-06-2016 03:55 PM
Sorry if this is the wrong place, this is my first post.
Basically I need to make >100 tables using the Output Delivery System. My proc tabulate statement itself works fine on its own, but the ods is not.
ods html body = '&question.xls'
here is the proc tabulate that is working fine
ods html close;
Now, the excel file is successfully being created, but it is literally called &question.xls, when I would want it to be called value.xls so it doesn't continually overwrite itself.
If there's a way to throw an array in here and just make one excel file with all >100 tables, that would also be good. I've been using SAS for about a month so sorry if these are totally n00b questions and thank you for reading and for any help you can give me.
07-06-2016 05:41 PM
Hi, to answer your second question, you do NOT need a DO loop, if BY group processing will work for you. The first example makes one sheet for every BY group. I limited by BY groups to 3 just to keep it simple. The second example puts all 3 tables on 1 worksheet and uses the PAGE dimension instead of BY group processing. But, you have to shift from using ODS HTML to using ODS TAGSETS.EXCELXP, which is an XML-based destination that creates Microsoft Spreadsheet Markup Language XML output.
proc sort data=sashelp.shoes out=newshoes; where region in ('Asia', 'Canada', 'Pacific'); by region product; run; ** 1: makes one sheet for every BY group; ods tagsets.excelxp file='c:\temp\showby.xml' style=htmlblue options(sheet_interval='bygroup' sheet_name='#byval(region)' doc='Help'); proc tabulate data=newshoes; class product; var sales inventory; by region; table product all, sales='Sales'*(sum mean) inventory='Inventory'*max; run; ods tagsets.excelxp close; ** 2: makes one sheet with ALL the tables on that single sheet; ods tagsets.excelxp file='c:\temp\showsingle.xml' style=htmlblue options(sheet_interval='none' sheet_name='All Regions' doc='Help'); proc tabulate data=newshoes; class region product; var sales inventory; table region, product all, sales='Sales'*(sum mean) inventory='Inventory'*max /box=_page_; run; ods tagsets.excelxp close;