ODS tagset xml help

[omega1983]

data rpt1

      rpt2;

set main1;

if id=1 then output r1

else output r2;

run;

ods listing close;

%macro findit(ds,name);

ods tagsets.ExcelXP

%mend;

%findit(r1,firstone)

%findit(r1,secondone)

I now want to do an xml output. using ods tagset.excelxp.  I want r1 to be in a tab and r2 to be in another tab.  When I have experimented, all the data eds up going into the r2 or seconone.  Is it best to do this with or without a global macro??

[Reeza]

You can only create an ods excelxp file in one run.

If you close the file you can't reopen it to append to it. You haven't shown the full macro so I can't say where the problem is but you could just display your data using a by group and using the option sheet_interval=bygroup instead. 

There's some good examples of that on this forum if you google a bit. Look for posts by Cynthia Zender.

[Cynthia_sas]

Hi,

  As Reeza suggests, you cannot append to a TAGSETS.EXCELXP XML file once you have closed it. My rule of thumb when working with macro programs is to start with a working SAS program, so you know what kind of code your macro program has to generate For example, if you had tried to create a 2-tab workbook, you could have done it in one ODS "sandwich" with one procedure step creating output for the first tab, and the second procedure step creating output for the second tab (taking all the defaults).

Generally, this means code similar to the code shown below, which creates a multi-sheet workbook. Once you get code like this working for your report, then, and only then, should you look to "macro-ize" your code. It doesn't help to macro-ize non-working code, that is nearly impossible to debug.

cynthia

ods tagsets.excelxp file='c:\temp\multsheet.xml'

    options(doc='Help' sheet_name='Starts with J')

    style=sasweb;

proc  print data=sashelp.class noobs;

  where substr(name,1,1) = 'J';

run;

ods tagsets.excelxp options(sheet_name='Starts A, B, C');

proc print data=sashelp.class noobs;

  where substr(name,1,1) in ('A','B','C');

run;

ods tagsets.excelxp close;