SAS中文论坛

标题: 练手系列3:表关联与累计值计算 [打印本页]

作者: shiyiming 时间: 2007-10-25 22:24
标题: 练手系列3:表关联与累计值计算
[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 时间: 2007-11-1 08:57
标题: Re: 练手系列3:表关联与累计值计算
响应的人不多呀，看样子题目有点难度，我想问下楼主，sql语句和sas代码的效率怎么来比较呢？有没有一个选择的标准？谢谢

作者: shiyiming 时间: 2007-11-1 17:51
标题: to xsmile
sql的效率比data step低很多，看过P3这本教材就知道了。

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

作者: shiyiming 时间: 2007-11-2 09:35
标题: Re: 练手系列3:表关联与累计值计算
不是很懂，没有看过p3，那我就用自己的方法搞定了，有时间的话帮我测试一下运行时间可以么？这样有个比较

作者: shiyiming 时间: 2007-11-2 09:48
标题: to xsmile
OK，重在参与。等你的程序公布出来，我帮你来测咯。 <img src="{SMILIES_PATH}/icon_cool.gif" alt="8)" title="Cool" />

作者: shiyiming 时间: 2007-11-5 17:05
标题: Re: 练手系列3:表关联与累计值计算
[code:1l4cbh61]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
;
proc sort;by name sdate edate;
run;

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

data bb1;
set a;by name;
sdate=date;
svalue=value;
if first.name then flag+1;
if first.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.flag=y.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.;run;[/code:1l4cbh61]

作者: shiyiming 时间: 2007-11-5 17:54
标题: Re: 练手系列3:表关联与累计值计算
写了段代码，楼主帮忙测测效率吧，多谢了~~

[code:21h2n8jf]

*源数据集;
data a;
infile datalines;
input name $ sdate:yymmdd10. value;
format sdate 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;

*step1:建立一个辅助的表，跟源表a保持一致，只是字段名源表b一致;
data a1;
infile datalines;
input name $ edate:yymmdd10. value;
format edate 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;

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

*得到期初期末净值;
rc1 = s.find();
svalue = value;
rc2 = e.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.find();
total = total + value;
end;
sdate = ldate;
run;

[/code:21h2n8jf]

作者: shiyiming 时间: 2007-11-5 18:11
标题: to waterlwh
写的很好，最后结果实现了功能要求。

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

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

大家再试试看。

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

作者: shiyiming 时间: 2007-11-6 15:27
标题: Re: 练手系列3:表关联与累计值计算
楼主的程序很好，让我再次翻了翻help，

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

作者: shiyiming 时间: 2007-11-6 18:09
标题: Re: 练手系列3:表关联与累计值计算
看了各位高手的答案，都不好意思把我的拿出来了：（
我是初学，考虑再三，拿出来请大家批评指正，不得已用了一点sql；
基本思路是在A中增加变量计算tvalue，再与B合并
[code:2espumwq]data a;
infile datalines;
input name $ date:yymmdd10. value;
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.;
datalines;
A 20070102 20070106
A 20070101 20070104
B 20070101 20070102
;
run;
proc sql;
create table m1 as
select a.value as svalue, b.* from a,b where a.name=b.name and a.date=b.sdate;
proc sql;
create table m2 as
select a.value as evalue, b.* from a,b where a.name=b.name and a.date=b.edate;
quit;
data m;
merge m1 m2;
by name;
run;
data m;
merge m1 m2;
by name;
run;
proc sql noprint;
select count(name) into:num from b;quit;
%macro luijb;
%do n=1 %to &num;
data _null_;
set b(firstobs=&n obs=&n);
call symput('name',name);
call symput('sdate',sdate);
call symput('edate',edate);

data a;
set a;
if name="&name" and (date-&sdate) ge 0 and (&edate-date) ge 0 then tvalue&n=value;

proc means data=a noprint;
var tvalue1-tvalue&n;
output out=c sum=tvalue1-tvalue&n ;
proc transpose data=c out=c1;
var tvalue1-tvalue&n;
%end;

data c;
set c1(rename=(col1=tvalue));
keep tvalue;
run;
%mend luijb;
%luijb;
data result;
merge c m;
format sdate edate yymmdd10.;
run;
[/code:2espumwq]

作者: shiyiming 时间: 2007-11-6 20:17
标题: Re: 练手系列3:表关联与累计值计算
最近两天忙，没有怎么来看，原来高手都来了呀,呵呵,我也顺便学习下

作者: shiyiming 时间: 2007-12-7 21:29
标题: Re: 练手系列3:表关联与累计值计算
[code:2u6senv4]data ahuige;
  set b;
  ID=_N_;
  do date=sdate to edate;
  output;
  end;
run;

proc sort data=AHUIGE;
  by name DATE;
run;

data expansion(WHERE=(NOT MISSING(ID)));
  merge a  ahuige;
  by name date;
  sval=(date=sdate)*value;
  eval=(date=edate)*value;
run;

proc sort data=expansion;
  by ID name sdate edate;
run;

DATA C(KEEP=NAME SDATE EDATE STARTVAL ENDVAL SUM);
  SET EXPANSION ;
  BY ID NAME SDATE EDATE;
  RETAIN STARTVAL ENDVAL SUM 0;
  STARTVAL=STARTVAL*(1-FIRST.NAME)+SVAL;
  ENDVAL=ENDVAL*(1-FIRST.NAME)+EVAL;
  SUM=SUM*(1-FIRST.NAME)+VALUE;
  IF LAST.NAME THEN OUTPUT;
RUN;[/code:2u6senv4]
不会HASH,也没有SAS9.................

作者: shiyiming 时间: 2007-12-25 18:52
标题: Re: 练手系列3:表关联与累计值计算
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;
data temp1;
retain total;
set a;
by name;
if first.name then total=0;
total=value+total;
lagtotal=lag(total);
if first.name then lagtotal=0;
run;

proc sort data=temp1;
by name date;
proc sort data=b out=temp2;
by name sdate;
run;
data temp2(rename=(value=svalue lagtotal=stotal) drop=total);
merge temp2(in=b1) temp1(rename=(date=sdate));
by name sdate;
if b1;
run;
proc sort data=temp2;
by name edate;
run;

data temp2(rename=(value=evalue) drop=lagtotal total stotal);
merge temp2(in=b1) temp1(rename=(date=edate));
by name edate;
retain name sdate edate svalue value tvalue;
if b1;
tvalue=total-stotal;
run;

也能实现，不知道在现实环境中的速度，因为里面有对B表排序。

作者: shiyiming 时间: 2007-12-26 09:31
标题: Re: 练手系列3:表关联与累计值计算
这个例子如果用MERGE在实际环境中很慢

作者: shiyiming 时间: 2008-3-27 08:34
标题: Re: 练手系列3:表关联与累计值计算
[code:3gic0mzb]
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
B 20070103 10
;
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
A 20070101 20070104
;
run;
data c(drop=name1 value date);
set b;
retain tvalue 0;
tvalue=0;
if _n_=1 then set a nobs=n;
do i=1 to n until(last);
set a (rename=(name=name1)) end=last point=i;
if name=name1 and date=sdate then svalue = value;
if name=name1 and date>=sdate and date<=edate then tvalue+value;
if name=name1 and date=edate then do;
evalue=value;
output;
end;
end;
run;
proc print data=c;
run;
[/code:3gic0mzb]
正在学习sas，看到题目忍不住写了一个程序，也能实现，不过至于效率嘛。。。。。见笑了

作者: shiyiming 时间: 2008-4-8 21:10
标题: Re: 练手系列3:表关联与累计值计算
楼主，今天在某银行卡中心拜读了跟你名字一样的人写的sas程序啊，
不会真就是你吧，呵呵，
方便的话qq联系啊，15976493

作者: shiyiming 时间: 2008-4-9 13:00
标题: to zhoubin
没错，那就是我。小弟不才，就是在银行里混的，呵呵呵。
我在国内绝大多数的信用卡中心工作过或者合作过，与这些卡中心的员工、系统、数据都很熟。感觉都像是我的“家”一样。
银行业圈内小有名气，至少在很多银行的IT部、市场部、风险部都有点人缘。

作者: shiyiming 时间: 2008-6-3 10:29
标题: Re: 练手系列3:表关联与累计值计算
对于小表与大表的连接,用format的方法应该效率不错,不比HASH函数慢.

作者: shiyiming 时间: 2008-6-4 17:39
标题: to bobhoo
兄弟说的很对，另外感觉hash比较危险，
因为它把数据全部装入内存中，
对于大数据集，
一旦内存不足的话，就容易失败。

作者: shiyiming 时间: 2008-7-15 13:18
标题: Re: 练手系列3:表关联与累计值计算
大家可以比较一下各种方法的效率，[url=http://www.sas.com/offices/NA/canada/newsletter/insights/sep05/SAS9_Features_Function.pdf:2ria7ohe]参考文档[/url:2ria7ohe]。
[img:2ria7ohe]http://p13.facemm.net/p.aspx?u=v20_p13_p_0807151312428702_0.jpg[/img:2ria7ohe]

作者: shiyiming 时间: 2010-12-19 17:21
标题: Re: 练手系列3:表关联与累计值计算
典型的table lookup问题
十分巨大的master数据集，非常小的交易数据集，这种情况对交易数据集做索引，然后查询，速度非常快，不比什么hash表之类的慢，因为相邻数据已经预读入cache了
我经常做这种table lookup，发现在这种情况下hash表还真没有啥优势，也许我们公司的存储系统调教的比较好，如果是在PC上搞的话，把a表调入内存library或者用sasfile也许更好

[code:2wmz580v]
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;

proc datasets library=work nolist;
      modify a;
index create idx=(name  date);
run;quit;

data c;
      set b;
date=sdate;
set a key=idx;
svalue=value;  evalue=.; tvalue=value;
do date=(sdate+1) to edate;
      set a key=idx;
   select (_iorc_);
         when(%sysrc(_sok))    tvalue=tvalue+value;
   when(%sysrc(_dsenom))  _error_=0;
   otherwise;
end;
      end;
evalue=value;
keep name sdate  edate  svalue  evalue  tvalue;
run;
[/code:2wmz580v]

作者: shiyiming 时间: 2010-12-19 19:26
标题: Re: 练手系列3:表关联与累计值计算
翻译一下：“叶问师傅，谁让你在这里教拳的？咏春有这么厉害吗？怕是遇到洪拳就不给力了吧？！” <img src="{SMILIES_PATH}/icon_lol.gif" alt=":lol:" title="Laughing" />
开个玩笑。的确hash在实际应用中没那么广泛和出色，的确现需要与系统组配合先做系统调优，真不太好搞。一语中的，一针见血啊。

欢迎光临 SAS中文论坛 (https://mysas.net/forum/)