SAS---DDE Export

example1

打开excel report_template.xlsb，然后运行代码

%macro dde(excel=,sheet=,data=,r1=,r2=,c1=,c2=,out=);
    filename dde_out dde "Excel|[&excel..xlsb]&sheet!R&r1.C&c1.:R&r2.C&c2." notab lrecl=2048; 
    data _null_;
        set &data.;
        file dde_out dsd dlm='09'x ;
        put &out;
    run;
%mend;
%dde(excel=report_template, sheet=sheet_name, data=SAS_dataset, r1=7,r2=22,c1=3,c2=10,out=col1_name col2_name col3_name);

example2

%macro dde(excel=,sheet=,data=,r1=,r2=,c1=,c2=,out=);
    filename dde_out dde "Excel|[&excel..xls]&sheet!R&r1.C&c1.:R&r2.C&c2." notab lrecl=2048;
    data _null_;
        set &data.;
        file dde_out dsd dlm='09'x ;
        put &out;
    run;
%mend;
%macro dde2(excel=,sheet=,data=,r1=,r2=,c1=,c2=,out=);
     filename dde_out dde "Excel|[&excel..xls]&sheet!R&r1.C&c1.:R&r2.C&c2.";
     data _null_;
             set &data.;
              file dde_out dsd dlm='09'x;
             put &out;
     run;
%mend;
%macro grp(sid,tp,dim,cnt,base,con);
    proc sql;
        create table r&sid. as
            select cats("&tp._",&dim.)as dimension,&cnt. as _&mon.
            from &base.
            &con.
            group by &dim.;
    quit;
%mend;
%grp(1,n_txn,c_purpose,count(*),Txn_level&day.,);

%macro combine;
    Data tmp.Result&mon.;
        set
            %do i = 1 %to 23;
                R&i.
            %end;
        ;
        proc sort; by dimension;
    Run;
%mend;
%combine;

%macro combine2;
    Data Result;
        merge
            %do i = 1 %to 13;
                %if %sysfunc(exist(tmp.Result&&LM&i..)) %then %do;
                    tmp.Result&&LM&i..
                %end;
            %end;
        ;
        by dimension;
    Run;
%mend;
%combine2;
%dde2(excel=COUNTERFX_&mon._v2, sheet=result, data=Result, r1=1,r2=1,c1=1,c2=14,out="Dimension " "&LM13. " "&LM12. " "&LM11. " "&LM10. " "&LM9. " "&LM8. " "&LM7. " "&LM6. " "&LM5. " "&LM4. " "&LM3. " "&LM2. " "&LM1. ");
%dde(excel=COUNTERFX_&mon._v2, sheet=result, data=Result, r1=2,r2=500,c1=1,c2=14,out=dimension _&LM13. _&LM12. _&LM11. _&LM10. _&LM9. _&LM8. _&LM7. _&LM6. _&LM5. _&LM4. _&LM3. _&LM2. _&LM1.);