ODS Excel Macro Overwritten SAS

Question

Here is the code

%macro ODS1 (Type=, var=, data=); ODS listing close;
ODS tagsets.excelXP file = "H:\Liv_Divide\Dispersion1.xml";
proc format;
    value myfmt .='#N/A'
    other = [11.2];
run;
 ODS Tagsets.ExcelXP
                options (embedded_titles="Yes" Sheet_Name="&Type");
    proc print data = &data (drop = Type) noobs style (header) =         [font_style=italic
                                                                         font_weight=bold
                                                                         background=#FF9900];
                                                                       var _all_ / style (data) = [background=White];`

    format _numeric_ myfmt.;
    format Date MONYY.;

    title1 "Measures of Cross-Sectional Dispersion for &var(&Type)";
    title2 "Dispersion Measure D1 = 75th Percentile Minus 25th Percentile of the Forecasts for Levels"; 
    run; quit;
    ODS Tagsets.ExcelXP close;
    ODS listing; 
            `%mend ODS1;`

           `%ODS1 (type=RGDPX,  data=Dispersion1_RGDPX, var=REAL GROSS DOMESTIC PRODUCT);
            %ODS1 (type=GDPX,   data=Dispersion1_GDPX,  var=GROSS DOMESTIC PRODUCT);
            %ODS1 (type=BFIX,   data=Dispersion1_BFIX,  VAR=BUSINESS FIXED INVESTMENT);
            %ODS1 (type=CPAT,   data=Dispersion1_CPAT,  VAR=CORPORATE PROFITS AFTER TAX);
            %ODS1 (type=IP,     data=Dispersion1_IP,    VAR=INDUSTRIAL PRODUCTION);
            %ODS1 (type=TPHS,   data=Dispersion1_TPHS,  VAR=TOTAL PRIVATE HOUSING);
            %ODS1 (type=PPI,    data=Dispersion1_PPI,   VAR=PRODUCER PRICE INDEX);
            %ODS1 (type=CPI,    data=Dispersion1_CPI,   VAR=CONSUMER PRICE INDEX);
            %ODS1 (type=UNPR,   data=Dispersion1_UNPR,  VAR=UNEMPLOYMENT RATE);
            %ODS1 (type=WMFG,   data=Dispersion1_WMFG,  VAR=WEEKLY EARNINGS);
            %ODS1 (type=RTTR,   data=Dispersion1_RTTR,  VAR=RETAIL SALES);
            %ODS1 (type=AUTODF, data=Dispersion1_AUTODF, VAR=AUTOSALES);
            %ODS1 (type=PRIME,  data=Dispersion1_PRIMEK, VAR=PRIME RATE);
            %ODS1 (type=TBOND,  data=Dispersion1_TBOND,  VAR=TBOND RATE);
            %ODS1 (type=TBILL,  data=Dispersion1_TBILL,  VAR=TBILL RATE);
            %ODS1 (type=SPIF,   data=Dispersion1_SPIF,   VAR=STANDARD & POORS);`

The problem is when I run the code, instead of producing separate sheets for each type, the data is overwritten in a way that I only get the Excel file with one worksheet for the very last Type (i.e. SPIF). So I end up with only one sheet. Is there anyway that when I run the Macro, that it will produce a separate sheet for each type without overwriting one sheet with another when I call the macro for each type.

Please don't SHOUT when posting your question. Typing in ALL CAPS makes text harder to read, it's rather annoying, and it won't help you get answers any sooner. The Shift key exists on both sides of the keyboard to make it easier to reach, and properly cased text is more readable and easier on the eyes. Thanks. — Ken White

DomPazz DomPazz · Accepted Answer · 2014-04-25T21:09:56

you need to move the ODS tagsets.excelXP file = and ODS Tagsets.ExcelXP close; statements outside of the macro. You are overwriting the file each time.

Here is a simplified version of your code that works:

%macro ODS1 (Type=, var=, data=); ODS listing close;
 ODS Tagsets.ExcelXP
    options (embedded_titles="Yes" Sheet_Name="&type");

proc print data = &data(obs=10) noobs;
run;
run; quit;

%mend ODS1;

ODS tagsets.excelXP file = "c:\temp\Dispersion1.xml";

%ods1(type=cars,data=sashelp.cars);
%ods1(type=shoes,data=sashelp.shoes);

ODS Tagsets.ExcelXP close;
ODS listing;

ODS Excel Macro Overwritten SAS

1 Answers