练手系列3:表关联与累计值计算

shiyiming

[code:1tr38g9w]data a;
infile datalines;
input name $ date:yymmdd10. value;
format date yymmdd10.;
datalines;
A 20070101 1
A 20070102 2
A 20070103 3
A 20070104 4
A 20070105 5
A 20070106 6
A 20070107 7
B 20070101 8
B 20070102 9
;
run;

data b;
infile datalines;
input name $ sdate:yymmdd10. edate:yymmdd10.;
format sdate edate yymmdd10.;
datalines;
A 20070102 20070106
A 20070101 20070104
B 20070101 20070102
;
run;[/code:1tr38g9w]
解释：
1、A表是每日基金净值表，其中name是基金名称；date是收盘日期；value是当日净值。
2、B表是各客户持有各基金的起始日期与终止日期，其中name是基金名称，sdate是起始日期，edate是终止日期，客户号省略了。
3、真实环境中：A表不大，只有几千条记录；B表巨大，会有一亿条记录。

要求：
1、目标表C含有各客户持有各基金起始日期的净值；终止日期的净值；期间净值累计值。即在保留B表所有字段的基础上衍生计算三个字段：svalue（起始日期的净值）；evalue（终止日期的净值）；tvalue（期间净值累计值）。
[quote:1tr38g9w]A 20070102 20070106 2 6 20
A 20070101 20070104 1 4 10
B 20070101 20070102 8 9 17[/quote:1tr38g9w]
2、尽可能考虑两个表的大小关系，用你认为合适的、高效的方式实现。
3、请勿用proc sql实现，否则起不到练手作用，并且用sql写效率低下，难以在真实环境中为人接受。

shiyiming

响应的人不多呀，看样子题目有点难度，我想问下楼主，sql语句和sas代码的效率怎么来比较呢？有没有一个选择的标准？谢谢

shiyiming

sql的效率比data step低很多，看过P3这本教材就知道了。

倒是没有标准答案，在大家尝试以后我会公布我的方法，大家相互参考可以比较一下各自程序的性能，这样提高的快。关键是一起来活动一下脑子，重在参与嘛。

shiyiming

不是很懂，没有看过p3，那我就用自己的方法搞定了，有时间的话帮我测试一下运行时间可以么？这样有个比较

shiyiming

OK，重在参与。等你的程序公布出来，我帮你来测咯。 <!-- s8) --><img src="{SMILIES_PATH}/icon_cool.gif" alt="8)" title="Cool" /><!-- s8) -->

shiyiming

[code:1l4cbh61]data a;
infile datalines;
input name $ date&#58;yymmdd10&#46; value;
format date yymmdd10&#46;;
datalines;
A 20070101 1
A 20070102 2
A 20070103 3
A 20070104 4
A 20070105 5
A 20070106 6
A 20070107 7
B 20070101 8
B 20070102 9
;
run;

data b;
infile datalines;
input name $ sdate&#58;yymmdd10&#46; edate&#58;yymmdd10&#46;;
format sdate edate yymmdd10&#46;;
datalines;
A 20070102 20070106
A 20070101 20070104
B 20070101 20070102
;
proc sort;by name sdate edate;
run;

data bb;
set a;by name;
edate=date;
evalue=value;
if first&#46;name then flag+1;
if first&#46;name then value1=value;else
value1+value;
drop date value;
run;

data bb1;
set a;by name;
sdate=date;
svalue=value;
if first&#46;name then flag+1;
if first&#46;name then value2=value;
else value2+value;
drop date value;
run;

proc sql;
create table temp as
select * from bb1 as x, bb as y
where x&#46;flag=y&#46;flag;
quit;

data final;
merge b(in=a) temp(in=b);
by name sdate edate;
if  a and b;
tvalue=value1-value2+svalue;
drop flag value1 value2;
run;
proc print noobs;
format sdate edate yymmdd10&#46;;run;[/code:1l4cbh61]

shiyiming

写了段代码，楼主帮忙测测效率吧，多谢了~~

[code:21h2n8jf]



*源数据集;
data a;
        infile datalines;
        input name $ sdate&#58;yymmdd10&#46; value;
        format sdate yymmdd10&#46;;
        datalines;
        A 20070101 1
        A 20070102 2
        A 20070103 3
        A 20070104 4
        A 20070105 5
        A 20070106 6
        A 20070107 7
        B 20070101 8
        B 20070102 9
        ;
run;

data b;
        infile datalines;
        input name $ sdate&#58;yymmdd10&#46; edate&#58;yymmdd10&#46;;
        format sdate edate yymmdd10&#46;;
        datalines;
        A 20070102 20070106
        A 20070101 20070104
        B 20070101 20070102
        ;
run;


*step1&#58;建立一个辅助的表，跟源表a保持一致，只是字段名源表b一致;
data a1;
        infile datalines;
        input name $ edate&#58;yymmdd10&#46; value;
        format edate yymmdd10&#46;;
        datalines;
        A 20070101 1
        A 20070102 2
        A 20070103 3
        A 20070104 4
        A 20070105 5
        A 20070106 6
        A 20070107 7
        B 20070101 8
        B 20070102 9
        ;
run;

*step2&#58;计算累计净值;
data c(keep = name sdate edate svalue evalue total);
        format name $ 8&#46;;
           format sdate yymmdd10&#46;;
        format edate yymmdd10&#46;;
        format ldate yymmdd10&#46;;
        format value best12&#46;;
           if _N_ = 1 then do;
             declare hash s(dataset&#58; &quot;work&#46;a&quot;, hashexp&#58; 6);
              s&#46;defineKey('name','sdate');
             s&#46;defineData('value');
             s&#46;defineDone();
                declare hash e(dataset&#58; &quot;work&#46;a1&quot;, hashexp&#58; 6);
              e&#46;defineKey('name','edate');
             e&#46;defineData('value');
             e&#46;defineDone();
           end;
        set b;

        *得到期初期末净值;
        rc1 = s&#46;find();
        svalue = value;
        rc2 = e&#46;find();
        evalue = value;

        *得到期间累计净值;
        count = edate - sdate;
        ldate = sdate ;
        total = 0;
        value = 0;
        do i = 0 to count;
                name  = name ;
                sdate = ldate;
                sdate = sdate + i;
                rc    = s&#46;find();
                total = total + value;
        end;
        sdate = ldate;
run;



[/code:21h2n8jf]

shiyiming

写的很好，最后结果实现了功能要求。

中间这步proc sql是把日期之间的间隔情况的所有可能性都算出来了。试想如果有10个基金，6年的记录，岂不是会产生10*365*6*365*6条记录，这张中间表的体积也够惊人的，大约5000万条记录。最后把这张中间表temp与1亿条客户持有信息表B放在一起merge需要好几个小时啊。

不过这个程序比起将持有信息表B转换成每天一条记录的中间表这一思路应该是好很多了。这个练习就是要强调考虑性能，因为功能还是不难实现的。因此我的题目中特别强调2个原始数据表的大小，所以尽可能用一个data步搞定，并且“严禁”使用效率低下的proc sql。

大家再试试看。

shiyiming

我可是含着眼泪读完你的程序的，因为你的思路几乎与我一摸一样。哈哈哈 <!-- s:lol: --><img src="{SMILIES_PATH}/icon_lol.gif" alt=":lol:" title="Laughing" /><!-- s:lol: -->
当然具体实现还是有一点点差别，既然有TX的程序与我的很接近了，那我先把我的程序贴出来。不过我坚信还有更高效的方法，只是我们还没有想到。希望大家继续讨论，再完善下去。这样的讨论一定能使我们的水平都得到提高的！
[code:3kbi7wyc]data c;
if 0 then set a;
if _n_=1 then do;
        declare hash share(dataset&#58;'work&#46;a', ordered&#58;'ascending');
        share&#46;definekey ('name', 'date');
        share&#46;definedata(all&#58;'yes');
        share&#46;definedone();
end;
set b;
rc=share&#46;find(key&#58;name, key&#58;sdate);
svalue=value;
rc=share&#46;find(key&#58;name, key&#58;edate);
evalue=value;
tvalue=0;
do i=sdate to edate;
        rc=share&#46;find(key&#58;name, key&#58;i);
        tvalue+value;
end;
keep name sdate edate svalue evalue tvalue;
run;[/code:3kbi7wyc]
我的程序在真实环境中测试，花了45分钟左右。

shiyiming

楼主的程序很好，让我再次翻了翻help，

进一步加深了对hash的用法，多谢了~~