SQL程序求救！

shiyiming

我有两个dataset：aq （variable: gvkey, datadate）, cdsall (variable: gvkey, date, cds), 对应aq中的每一个observation，我想要前面第3、2、1个quarter，之后1个quarter，以及这4个quarter的cds的mean和median。

我的程序如下：

[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多分钟了还没出结果。

aqlist中有37xx个observations，cdsall里面比较有40多万个observations。有什么优化便捷的方法实现我想要的结果么？

谢谢！

shiyiming

[code:p0mj5wx4]data aq;
        input gvkey datadate yymmdd8.;
        format datadate yymmdd10.;
datalines;
1 20100510
1 20100515
2 20100515
;
data cdsall;
        input gvkey date yymmdd8. cds;
        format date yymmdd10.;
datalines;
1 20100501 1
1 20100505 2
1 20100510 3
1 20100515 4
1 20100520 5
1 20100525 6
2 20100501 1
2 20100505 2
2 20100510 3
2 20100515 4
2 20100520 5
2 20100525 6
;
data temp(drop=key);
        if _n_=1 then
                do;
                        declare hash h(hashexp:16,dataset:'aq');
                        declare hiter iter('h');
                        h.definekey('gvkey','datadate');
                        h.definedata('gvkey','datadate');
                        h.definedone();
                        call missing(gvkey,datadate);
                end;
        set cdsall(rename=(gvkey=key));
        do while(iter.next()=0);
                if key=gvkey then
                        do;
                                select;
                                        when(datadate<=date<intnx('week',datadate,1,'sameday')) group='A1';
                                        when(intnx('week',datadate,-1,'sameday')<=date<datadate) group='B1';
                                        otherwise call missing(group);
                                end;
                                if not missing(group) then output;
                        end;
        end;
        format datadate yymmdd10.;
run;[/code:p0mj5wx4]

shiyiming

谢谢hope大牛，你总能给我hope，hoho

不过实际上我数据里的gvkey不是数值形式的，而是字符形式的，为$8.，这样我在运行你的code的时候，总是告诉我error:type mismatch for key variable，这是什么问题？我找了半天第一段hash data里面没有设置dataformat的地方啊？

shiyiming

在data temp;后面加一句
[code:2lko5fnv]length gvkey $8;[/code:2lko5fnv]
建议先用少量数据测试一下,因为我不确定效率如何,我是村里的,没见过40万的obs

shiyiming

hug hug，确实快了很多，之前我算了2天还没算出来的东西，现在35分钟就出来了。