data buy(rename=(date=indate) drop=i num) sell(drop=i num);
set ahuige;
if act=1 then
do i=1 to num;
output buy;
end;
if act=0 then
do i=1 to num;
output sell;
end;
run;
data temp;
merge buy sell;
by id;
output;
date=.;
run;
proc sql;
create table final as
select distinct id, date, act, avg(date-indate) as avgstore
from temp( where=(act=0))
group by id,date ;
[/code:2r5gcp62]作者: shiyiming 时间: 2009-8-5 13:19 标题: Re: 求助：怎么将一行数据和多行数据进行匹配，然后生成新变量？ 谢谢ahuige
你这个程序是比较简洁，不过如果数据很多的话（比如有100万以上的买记录和卖记录，每个记录的数量num又很大），产生的BUY和SELL会极其大吧，这个问题有没有办法克服掉呢？作者: shiyiming 时间: 2009-8-5 13:32 标题: Re: 求助：怎么将一行数据和多行数据进行匹配，然后生成新变量？ 很多情况下要代码简洁就有可能要有效率的代价。如果是有100万条等对运算效率的死要求，写法又会不一样了。
不过你的新问题有点含糊，
平均分到没卖完的每组，然后相当于是按各组买的日期分别来算？如果不能平均分怎么办？
现在忙，有时间以后再给你看。作者: shiyiming 时间: 2009-8-5 17:02 标题: Re: 求助：怎么将一行数据和多行数据进行匹配，然后生成新变量？ DRAFT东西写了，但没时间QC，明天看看再发出来。作者: shiyiming 时间: 2009-8-5 18:01 标题: Re: 求助：怎么将一行数据和多行数据进行匹配，然后生成新变量？ 恩，多谢多谢，我等～～作者: shiyiming 时间: 2009-8-6 09:40 标题: Re: 求助：怎么将一行数据和多行数据进行匹配，然后生成新变量？ [code:2utn7j7u]data store sellandgift;
infile datalines truncover;
input ID$ Date yymmdd8. Num Act Note ;
format Date yymmdd10.;
if act=1 and note=. then output store;
else output sellandgift;
datalines;
001 08-07-01 100 1 .
001 08-07-02 10 0 .
001 08-07-06 30 0 .
001 08-07-10 100 1 .
001 08-07-15 60 0 .
001 08-07-16 100 1 .
001 08-07-18 160 1 0
001 08-07-22 100 0 .
001 08-07-25 220 0 .
002 08-07-01 100 1 .
003 08-07-02 100 1 .
003 08-07-03 50 1 .
003 08-07-04 100 0 .
003 08-07-08 30 0 .
003 08-07-09 20 0 .
;
run;
%let offsetid=0;
%macro updateStore(num,note,id,selldate) ;
%let offsetid=%eval(&offsetid+1);
%if &note=. %then
%do;
data store(keep=id date num act note) avg(keep=id date num diff);
set store ;
by id date;
retain number &num diff 0;
if id=&id then
do;
offnum=min(num,number);
num=num-offnum;
number=number-offnum;
diff=diff+(&selldate-date)*offnum;
end;
if num>0 then output store;
if id=&id and last.id then do;diff=diff/#date=&selldate;output avg; end;
run;
proc append base=allavg
data=avg;
run;
%end;
%if &note=0 %then
%do;
proc sql;
create table store(drop=oldnum ) as
select *,oldnum+oldnum/sum(oldnum)*&num*(id="&id") as num
from store(rename=(num=oldnum))
group by id
order by id,date;
%end;
%mend;
data b(keep=id date num rename=(date=remain_date num=remain_num)) c(keep=id ft_obs );
set a;
by id date;
if first.id then do;ft_obs=_n_;output c; end;
output b;
where act=1 ;
run;
data result;
set a;
by id;
if _n_=1 then do;
length id $8 ft_obs 8;
declare hash myhash(dataset:"c");
myhash.definekey("id");
myhash.definedata("ft_obs");
myhash.definedone();
call missing(ft_obs);
end;
if first.id then do;
_rc=myhash.find();
retain move_point;
move_point=ft_obs;
length temp_remain_date temp_remain_num 8;
retain temp_remain_date temp_remain_num;
set b(drop=id) point=move_point;
temp_remain_date =remain_date ;
temp_remain_num =remain_num;
end;
if act=0 then do;
_temp_rm_num=num;
startget:;
_temp_rm_num=sum(temp_remain_num,-_temp_rm_num);
if _temp_rm_num < 0 then do;
rlt_day=sum(rlt_day,(date-temp_remain_date)*temp_remain_num/num);
move_point+1;
set b(drop=id) point=move_point;
temp_remain_date =remain_date ;
temp_remain_num =remain_num;
_temp_rm_num =-_temp_rm_num;
goto startget;
end;
else do;
rlt_day=sum(rlt_day,(date-temp_remain_date)*(temp_remain_num-_temp_rm_num)/num);
temp_remain_num=_temp_rm_num;
end;
end;