ODS Export and merge output in one tab

[Q1983]

data have1;

length Group $50;

input Group $ _NAME_ $ Value date $;

datalines;

WWWW AARP 50 1jun2018

TTTT BBRP 123 5may2015

DDDD BBRP 123 5may2015

run;

data have2;

length Group $50;

input Group $ _NAME_ $ Value date $;

datalines;

DDDD AARP 50 1jun2018

QQQQ BBRP 123 5may2015

CCCC BBRP 123 5may2015

run;

data have3;

length Group $50;

input Group $ _NAME_ $ Value date $;

datalines;

AAAA AARP 50 1jun2018

BBBB BBRP 123 5may2015

DDDD BBRP 123 5may2015

run;

%macro Final(final,sheet_nm);

ODS TAGSETS.ExcelXP

options(sheet_interval='none'

default_column_width='9'

sheet_name=&sheet_nm.

center_horizontal="no"

frozen_headers = "Yes"

Orientation='Landscape'

embedded_titles='Yes'

fittopage="No"

blackandwhite="No"

Embedded_Footnotes='Yes'

absolute_column_width='9'

autofit_height="Yes");

PROC REPORT DATA= &final. headskip split='*' wrap nowd;

COLUMNS _all_;

RUN;

　

　

%mend final;

%Final(HAVE1,"HAVE");

%Final(HAVE2,"HAVE2");

%Final(HAVE3,"HAVE2");

The above mentioned code is designed to output 3 different tabs of output, have 1 have2 and have3

I want to output have1 separately however I want to output have2 and have 3 in the same tab and identify each with its unique title

TAB1

have1

TAB2

have2 "have2"

have3 "have3"

How could I do this and would I need a second macro statement to capture the unique tite

[Cynthia_sas]

Hi:

  Just a few comments...HEADLINE, HEADSKIP and WRAP are LISTING only options and will be ignored by ODS destinations. It's always a good idea to start with a working program before you "macroize" the program. Here's an "unmacro'd" program that does what you want (not as many options as what you specified-- just enough to show the correct syntax for what you want to do):

ods tagsets.excelxp file='c:\temp\mult_sheet.xml' style=htmlblue;

ods tagsets.excelxp options(sheet_name="Have");
proc report data=sashelp.class;
column _all_;
run;

ods tagsets.excelxp options(sheet_name="Have2" sheet_interval="none");
proc report data=sashelp.classfit;
  column _all_;
run;

proc report data=sashelp.iris(obs=10);
  column _all_;
run;
ods tagsets.excelxp close;

When you open that output file, you should see  only 2 sheets, Have1 and Have2. You don't need an additional ODS TAGSETS.EXCELXP statement before the 3rd PROC REPORT step -- not if you want both procedure outputs to go on the same sheet.

Hope this helps point you in the right direction.

Cynthia