%macro ww(a,b);
options validvarname=any;
proc import out=lwd.aug
datafile="C:\Users\Administrator\Desktop\充值记录\&a..xls"
dbms=excel replace;
run;
data lwd.aug_1;
set lwd.aug;
实际=input(实际到账金额,20.);
run;
proc sql;
create table lwd.aug_2 as
select 用户名,支付方式,实际 from lwd.aug_1 where 用户名 not in (select
borrower.用户名 from lwd.borrower) ;
quit;
proc sql;
create table lwd.aug_3 as
select 用户名, 支付方式,实际 from lwd.aug_2
proc sql;
create table lwd.aug_4 as
select 用户名,sum(实际) as 充值金额 from lwd.aug_3 group by 用户名;
quit;
proc import out=lwd.aug26tx
datafile="C:\Users\Administrator\Desktop\全部\&b..xls"
dbms=excel replace;
sheet='tx';
run;
proc import out=lwd.aug_26cz
datafile="C:\Users\Administrator\Desktop\全部\&b..xls"
dbms=excel replace;
sheet='cz';
run;
proc import out=lwd.aug_26invest
datafile="C:\Users\Administrator\Desktop\全部\&b..xls"
dbms=excel replace;
sheet='invest';
run;
proc sql;
create table lwd.aug_26t as
select 用户名,sum(操作金额) as 提现金额 from Lwd.Aug26tx where 用户名 not in (select
borrower.用户名 from lwd.borrower) group by 用户名;
quit;
proc sql;
create table lwd.aug_26i as
select 用户名,sum(操作金额) as 投资金额 from Lwd.Aug_26invest group by 用户名;
quit;
proc sql;*/去掉borrower中的借款人/*;
create table lwd.aug_26c as
select 用户名,sum(操作金额) as 充值金额 from Lwd.Aug_26cz where 用户名 not in (select
borrower.用户名 from lwd.borrower) group by 用户名;
quit;
proc sort data=lwd.aug_26c;
by 用户名;
run;
proc sort data=lwd.aug_4;
by 用户名;
run;
data lwd.merge;*/不用了/*;
merge lwd.aug_3 lwd.aug_26c;
by 用户名;
run;
proc sort data=lwd.aug_26t;
by 用户名;
run;
proc sort data=lwd.aug_26i;
by 用户名;
run;
*/筛选投资成功中的约标和非约标的人/*;
proc sql;
create table lwd.aug_26i1 as
select 用户名,备注,操作金额 from Lwd.Aug_26invest where 备注 like '%约标%';
quit;
data lwd.aug26a;
set lwd.aug_26i1;
约标标种=scan(scan(scan(备注,2,">"),2,"】"),1,"月")||"月";
run;
proc sql;
create table lwd.aug_26i2 as
select 用户名,备注,操作金额 from Lwd.Aug_26invest where 备注 not like '%约标%';
quit;
data lwd.aug26b;
set lwd.aug_26i2;
正式标种=scan(scan(备注,2,">"),1,"月")||"月";;
run;
proc sort data=lwd.aug26a ;
by 用户名;
run;
proc sort data=lwd.aug26b ;
by 用户名;
run;
data aug26;
keep 用户名 约标标种 正式标种;
set lwd.aug26a lwd.aug26b;
by 用户名;
run;
proc transpose data=aug26 out=b name=约标 prefix=y;
by 用户名;
var 约标标种 ;
run;
proc sort data=b;
by 用户名;
run;
proc transpose data=aug26 out=c name=正式标 prefix=z;
by 用户名;
var 正式标种 ;
run;
proc sort data=c;
by 用户名;
run;
data bc;
merge b c;
by 用户名;
run;
data lwd.aug_26;
merge lwd.aug_4 lwd.aug_26i lwd.aug_26t bc ;
by 用户名;
run;
data lwd.aug26;*/将缺失值替换为0/*;
set lwd.aug_26;
array var _numeric_;
do over var;
if var =. then var=0;
end;
run;
proc export data=lwd.aug26
outfile='C:\Users\Administrator\Desktop\每日金额统计\活动2.xls'
dbms=excel replace;
sheet="&a";
run;
%mend ww;
%ww(sep2,b);