The SAS Output Delivery System and reporting techniques

Using Macro Variable in ODS

Reply
New Contributor
Posts: 2

Using Macro Variable in ODS

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.

SAS Employee
Posts: 105

Re: Using Macro Variable in ODS

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

 

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

 

New Contributor
Posts: 2

Re: Using Macro Variable in ODS

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

SAS Employee
Posts: 105

Re: Using Macro Variable in ODS

Haha.. you're welcome. 

 

Ray

SAS Super FREQ
Posts: 8,647

Re: Using Macro Variable in ODS

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;
Grand Advisor
Posts: 16,416

Re: Using Macro Variable in ODS

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

Post a Question
Discussion Stats
  • 5 replies
  • 306 views
  • 0 likes
  • 4 in conversation