不知道说清楚了没有，谢谢 <!-- s:D --><img src="{SMILIES_PATH}/icon_biggrin.gif" alt=":D" title="Very Happy" /><!-- s:D -->作者: Qiong 时间: 2011-4-1 13:37 标题: Re: 请教一个从两个表提取数据的问题 [code:cact5um8]
proc sql;
create table v as select a.*,b.company
from b as b
left join a as a
on a.ID=b.id and b.date+3>=a.date>=b.date-3
order by id, company,date;
quit;[/code:cact5um8]作者: shiyiming 时间: 2011-4-1 22:59 标题: Re: 请教一个从两个表提取数据的问题 [quote="vicky1020":62d7hcyf][code:62d7hcyf]
proc sql;
create table v as select a.*,b.company
from b as b
left join a as a
on a.ID=b.id and b.date+3>=a.date>=b.date-3
order by id, company,date;
quit;[/code:62d7hcyf][/quote:62d7hcyf]
proc sql;
create table vtemp as select a.*,b.company,b.date as date_mid
from b as b
left join a as a
on a.ID=b.id ;
quit;
%macro a(yymmdd,id,lag=3);
%let con= %str(id="&id" and date_mid=&yymmdd.);
proc sql;
create table v1 as select *
from vtemp where date< &yymmdd. and &con.
order by date descending;
create table v2 as select *
from vtemp where date> &yymmdd. and &con.
order by date ascending ;
create table v3 as select *
from vtemp where date= &yymmdd. and &con.;
quit;
data vv_final ;
set vv_final v1(obs=&lag.) v3(obs=1) v2 (obs=&lag.) ;
run;
%mend a;
data vv_final;
set vtemp(obs=0);
data _null_;
set b ;
call execute('%a('||date||','||id||');');
call execute('run;');
run;
proc sql;
create table v1 as select *
from a where &yymmdd. -10<= date< &yymmdd. and id="&id"
order by date descending;
create table v2 as select *
from a where &yymmdd.+10 >= date> &yymmdd. and id="&id"
order by date ascending ;
create table v3 as select *
from a where date= &yymmdd. and id="&id" ;
quit;
data vv_final ;
set vv_final(in=a) v1(obs=&lag.) v3(obs=1) v2 (obs=&lag.) ;
if not a then campany="&company.";
run;
%mend a;
data vv_final;
set a(obs=0);
data _null_;
set b ;
call execute('%a('||date||','||id||','||company||');');
call execute('run;');
run;作者: Qiong 时间: 2011-4-2 17:29 标题: Re: 请教一个从两个表提取数据的问题 不知道你的a到底多大？如果还是太慢，run的太费劲的话，试试在a里对id，date建index，macro里面应该会快点。作者: shiyiming 时间: 2011-4-6 09:23 标题: Re: 请教一个从两个表提取数据的问题 谢谢vicky！
我的表A有170万条数据，表B有6000多条记录，lag=125,用了13个小时跑完，不知道问题出在哪里 <!-- s:? --><img src="{SMILIES_PATH}/icon_confused.gif" alt=":?" title="Confused" /><!-- s:? -->作者: Qiong 时间: 2011-4-6 09:52 标题: Re: 请教一个从两个表提取数据的问题 汗。。。。这个vtemp当然super super super 大了。。。。
对a里面加个index，然后把code里面第一层的选择放宽到lag+8，这个8是中国最长的长假天数
重新run应该不用那么久。。。
再不放心的话，先run data b的一百条，测试下速度。