Using Macro Variable in ODS

[jfriedman08]

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.

%let question=value;
ods html body = '&question.xls'
style=minimal;

              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.

[rayIII]

Hi. Just to address the first part of your question, change your first ODS statement to:

ods html body = "&question..xls" style=minimal;

[jfriedman08]

You are a gangsta of the highest order... thank you!!!!

[rayIII]

Haha.. you're welcome. 

Ray

[Cynthia_sas]

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.

cynthia

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;

[smitham]

Thank you! 

[Reeza]

And if you have SAS 9.4 you can look into ODS Excel.