topic Re: Using Macro Variable in ODS in ODS and Base Reporting

Using Macro Variable in ODS

jfriedman08 — Wed, 06 Jul 2016 19:55:56 GMT

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.

Re: Using Macro Variable in ODS

rayIII — Wed, 06 Jul 2016 20:08:15 GMT

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

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

Re: Using Macro Variable in ODS

jfriedman08 — Wed, 06 Jul 2016 20:11:15 GMT

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

Re: Using Macro Variable in ODS

rayIII — Wed, 06 Jul 2016 20:13:20 GMT

Haha.. you're welcome.

Ray

Re: Using Macro Variable in ODS

Cynthia_sas — Wed, 06 Jul 2016 21:41:06 GMT

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;

Re: Using Macro Variable in ODS

Reeza — Thu, 07 Jul 2016 02:40:46 GMT

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

Re: Using Macro Variable in ODS

smitham — Wed, 10 Oct 2018 17:07:18 GMT

Thank you!