两个数据集的操作-根据一个关键词主表连接从表的操作

现在有两个表,一个主表,一个从表,要求根据一个关键词将主表中的变量值加到从表中,从而得到一个合并的表。
下面是一个简单的代码示例:
data test1;
input id nowtime yymmdd10.;
format nowtime yymmdd10.;
cards;
100  20071201
200  20071101
;
run;
data test2;
input id histtime yymmdd10.;
format histtime yymmdd10.;
cards;
100  20020501
100  20030601
100  20040701
200  20030701
200  20040901
200  20051001
200  20061101
;
run;
 
目标数据集为:
  id       nowtime         histtime
100    2007-12-01    2002-05-01
100    2007-12-01    2003-06-01
100    2007-12-01    2004-07-01
200    2007-11-01    2003-07-01
200    2007-11-01    2004-09-01
200    2007-11-01    2005-10-01
200    2007-11-01    2006-11-01

实现这个要求的代码,目前我只想出来三种:
1,使用经典date步的merge方法:
data exx;
merge test1 test2;
by id;
run;
proc print;
run;
 
2,使用高级sql查询语句outer joins之left join:
proc sql;
create table test as
       select coalesce(test1.id, test2.id) label=’id’,
              nowtime,
              histtime
              from test1 left join test2
              on test1.id=test2.id;
quit;
 
3,使用sas9.0,9.1的DATA步增加的新功能:HASH对象方法
data  merged;
               length  id  8;
               length  nowtime 8;
               format nowtime yymmdd10.;  
               if _N_ = 1 then do ;
                    declare Hash ht (dataset:"test1");
                    ht.defineKey("id");
                    ht.defineData("nowtime");
                    ht.defineDone();
     call missing(id,nowtime);
               end;
          
              /* Load key and query hash table*/
            set test2;
               if  ht.find() =0 then output;
run;
这三种方法都可以实现。
但是那种会更好呢?我觉得三种都不错,熟悉那个就写那个,能解决问题就行,或者工作要求那个风格就用那个风格。
然而,实际中会遇到从表非常巨大的情况,这时三种方法的优劣就显现出来,那就是完成同样的要求代码执行时间不同(前提:代码成功且稳定可靠)。
既然其他方面的问题暂时步考虑,那下面就比较一下这三个方法的实现执行所需要的时间吧。
于是构造了下面两个数据集,其中第二个用循环得到一个巨大的数据集。
data master;
input key $ val1;
cards;
aaa 000
bbb 333
ccc 444
ddd 555
;
data trans;
      do val2= 0 to 26*300000-1;
      key=compress(byte(97+mod(val2,26))||byte(97+mod(val2,26))||byte(97+mod(val2,26)));
   output;
      end;
run;
/*感谢Ahuige提供Ascii代码转化思路!http://sasor.feoh.net/modules.php?name=Forums&file=viewtopic&t=1882&highlight=ASCII&sid=c32b5326ec07d4dd0197ab06939845c4*/
 
下面用上面提到的三种方法来进行这个操作,并比较这些方法所需要花费的时间。代码如下:
CODE1:
 
proc sql noprint;
  create table meraed as
      select coalesce(master.key, trans.key) label=’key’,
          val1,
    val2
             from master left join trans
        on master.key=trans.key;
     quit;
 

CODE2:
data  merged;
               length  key  $ 13;
               length  val1  8;
               if _N_ = 1 then do ;
                    declare Hash ht (dataset:" master");
                    ht.defineKey("key");
                    ht.defineData("val1");
                    ht.defineDone();
     call missing(key,val1);  /* avoid uninitialized variable notes */         
                    end;
          
              /* Load key and query hash table*/
            set trans;
               if  ht.find() =0;
run;
 
CODE3:
 
proc sort data=master out=master;
by key;
run;
proc sort data=trans out=trans;
by key;
run;
data meraed(where=(val1 ne .));
merge master trans;
by key;
run;
 
RESULTS:
 
A, 方法merge with by
      sort 花费时间:
           实际时间         2:01.90
           CPU 时间         10.84 秒
      merge花费时间:
          实际时间         9.90 秒
          CPU 时间         4.04 秒
(运行过程中,提示我C盘空间不够啊,说明中间临时数据集需要占不少资源 ^_^)
 
B, sql方法花费时间:
当事先排序时:
 sort过程花费时间:
           实际时间         2:01.90
           CPU 时间         10.84 秒
  sql 实际时间         7.34 秒
      CPU 时间         1.23 秒
 
当事先未排序时:
     实际时间          1:46.48
     CPU 时间         11.29 秒
(看来如果事前未排序,直接sql花费的总时间更短)
 
C, hash方法花费时间:
      实际时间          8.42 秒
      CPU 时间         3.60 秒
(貌似排不排序影响不大,不过时间波动很大(实际时间在范围5.81-10.46),可能系统太多程序同时进行)
 
小结:1,如果排好序,三种方法所花时间差不多。
         2,如果没有排好序,hash时间最小,其次为sql方法,时间最多为merge with by。
         3,merge with by要求必须先排好序,sql方法没有要求,如果数据集先已排好序,速度非常快,但是所用总时间大于直接用sql方法。hash方法也不要求排序,且排序是否不影响速度。
 
我的系统配置:台式机,XP系统;SAS9.13版;AMD双核3600+,内存1.5G,硬盘IDE接口。(当然还同时开了word,pdf,qq,note,xdict,winamp,iexpore,SAS9.13等程序)。
 
上面的数据具有一定的参考意义。
欢迎大家参与测试,并留下测试时间。
测试注意merge必须要事先排好序,而sql和hash方法则不需要,但好序后sql会更快。
 
欢迎大家扔砖!^_^
并且欢迎高手点评!^_^
 

相关博文

《两个数据集的操作-根据一个关键词主表连接从表的操作》上的一个想法

发表评论

电子邮件地址不会被公开。 必填项已用*标注