example1
打開(kāi)excel report_template.xlsb，然后運(yùn)行代碼
%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.);
