[code:28ryjsps]
proc sql;
create table aqlist as
select b.*, intnx('quarter', b.datadate, -3, 'sameday') as startdate format date9.,
intnx('quarter', b.datadate, -2, 'sameday') as twoqdate format date9.,
intnx('quarter', b.datadate, -1, 'sameday') as oneqdate format date9.,
intnx('quarter', b.datadate, 1, 'sameday') as enddate format date9.
from gvkeylist a, aq b
where b.gvkey=a.gvkey;
quit;
proc sql;
create table combine1 as
select a.*,
(select avg(b.cds) from cdsall b where a.gvkey=b.gvkey and a.startdate<=b.date<a.twoqdate) as avCDS_b3,
(select median(b.cds) from cdsall b where a.gvkey=b.gvkey and a.startdate<=b.date<a.twoqdate) as meCDS_b3,
(select avg(b.cds) from cdsall b where a.gvkey=b.gvkey and a.twoqdate<=b.date<a.oneqdate) as avCDS_b2,
(select median(b.cds) from cdsall b where a.gvkey=b.gvkey and a.twoqdate<=b.date<a.oneqdate) as meCDS_b2,
(select avg(b.cds) from cdsall b where a.gvkey=b.gvkey and a.oneqdate<=b.date<a.datadate) as avCDS_b1,
(select median(b.cds) from cdsall b where a.gvkey=b.gvkey and a.oneqdate<=b.date<a.datadate) as meCDS_b1,
(select avg(b.cds) from cdsall b where a.gvkey=b.gvkey and a.datadate<=b.date<a.enddate) as avCDS_a1,
(select median(b.cds) from cdsall b where a.gvkey=b.gvkey and a.datadate<=b.date<a.enddate) as meCDS_a2,
(select avg(b.cds) from cdsall b where a.gvkey=b.gvkey and a.startdate<=b.date<a.enddate) as avCDS_y,
(select median(b.cds) from cdsall b where a.gvkey=b.gvkey and a.startdate<=b.date<a.enddate) as meCDS_y
from aqlist a;
quit;
[/code:28ryjsps]
kaka，不知道大家是否看晕了，第一段SQL没有问题，只是提取出我想要的那些gvkey，并设了每个时间段的起始日期。现在我的程序在第二段SQL运行不过去，总说error：subquery for multi rows? (貌似是这个提示) ，如果写个macro，把aqlist中每一个observation提出来运行第二个sql就可以，但是非常非常慢。现在我把求median的部分都去掉也可以运行，当然也很慢，现在20多分钟了还没出结果。