ODS Excel宏覆盖SAS

Question

以下是代码

%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);`

问题是，当我运行代码时，不是为每种类型生成单独的工作表，而是以一种方式覆盖数据，即我只获得最后一种类型(即SPIF)的一个工作表的Excel文件。所以我最终只得到了一张纸。无论如何，当我运行Macro时，它会为每种类型生成一个单独的工作表，而不会在我为每种类型调用宏时用另一个工作表覆盖另一个工作表。

Answer 1

您需要将ODS tagsets.excelXP file =和ODS Tagsets.ExcelXP close;语句移到宏外。您每次都会覆盖该文件。

下面是你的代码的一个简化版本：

%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;