ODS Excel Macro Overwritten SAS -

Here is the code

 % macro ODS1 (type =, var =, data =) ; ODS listing closed; ODS tags EXXAP file = "H: \ live_bide \ Dispersion1.xml"; Proc format; Value myfmt = '# N / A' Other = [11.2]; Run; ODS Tags. Xxxp option (embedded_titles = "yes" sheet_nem = "and type"); Proc Print Data = & amp; Data (drop = type) node style (header) = [font_style = italic font_weight = bold background = # FF9900]; Var _all_ / style (data) = [background = white]; `Format _numeric_ myfmt .; Format date MONYY .; Cross-sectional breakdown measures for title 1 "& var (and type)"; Heading 2 "dispersion measures D1 = minimum 25th percentile 75 percent for level forecast"; Run; leave; ODS tags.excelexp off; ODS listing; `% MEND ODS1;` `% ODS1 (type = RGDPX, data = dispersion 1_rgdx, var = real total household product); % ODS1 (type = gdpx, data = dispersion1_gdpx, server = domestic product); % ODS1 (Type = BFIIX, Data = Faber 1 BIFIX, VAR = Business Fixed Investment); % ODS1 (type = CPAT, data = dispersion 1_CPAT, VAR = corporate profits after tax); % ODS1 (type = IP, data = dispersion1_ip, VAR = industrial production); % ODS1 (type = TPHS, data = dispersion 1_TPHS, VAR = total private housing); % ODS1 (type = ppi, data = dispersion 1_PPI, VAR = productive price index); % ODS1 (type = cpi, data = dispersion 1_cpi, vaar = consumer price index); % ODS1 (type = UNPR, data = dissection 1_UNPR, VAR = unemployment rate); % ODS1 (type = WMFG, data = dispersion 1_WMFG, VAR = weekly income); % ODS1 (type = rttr, data = dispersion1_tttr, vaR = retail cell); % ODS1 (type = AUTODF, data = dispersion 1_AUTODF, VAR = autosall); % ODS1 (type = PRIME, data = dispersion1_PRIMEK, VAR = staple rate); % ODS1 (type = TBOND, data = dispersion1_TBOND, VAR = tab rate); % ODS1 (type = TBIL, data = dispersion1_TBILL, VAR = TBEL rate); % ODS1 (Type = SIPIF, Data = Dursion 1_SPIF, VAR = Standard and Peer); `  
 The problem is that when I run the code, rather than creating separate sheets for each type of data, the data is overwritten in a way that I can only for the final file Excel file is available with a worksheet (i.e. SPIF) So I end up with only one sheet, is there anyway when I run the macro, when I call the macro for each type, then each letter A sheet area will produce a separate sheet for each type without writing to each other.   
 
 
 You have to move  ODS tagsets.excelXP file =  and  You are overwriting the file every time you write outside macros.  
 Here is a simple version of your code that works:  
 % macro ODS1 (type =, var =, data =); ODS listing closed; ODS Tags. Xxxp option (embedded_titles = "yes" sheet_nem = "& type"); Proc Print Data = & amp; Data (obs = 10) noobs; Run; Run; leave; % MEND ODS1; ODS tags EXXP file = "C: \ emp. \ Disposition 1.xml"; % Ods1 (type = cars, data = sashelp.cars); % Ods1 (type = shoes, data = sashelp.shoes); ODS tags.excelexp off; ODS listing;