SAS9中可以实现非排序merge

shiyiming

对于作ETL程序的sasor来说，比较头疼的处理是在merge操作以前一定要对数据集进行排序，数据量小还好说，一旦处理大数据量，排序就成了比较费时的操作。
偶在项目中得到一位香港朋友的帮助，发现在sas9利用hash table对大表与小表的拼接完全可以省去sort，特与广大的sasor共享：
data data1;
        input a $ b $;
cards;
a 1
d 10
e 23
b 2
a 3
a 2
c 5
;

data data2;
        input a $ c $;
cards;
a za
b za
a zb
a zc
;

data tmpdata;
        set data2;
        if c = 'za';
run;

data out(drop=rc);
  length a c $ 8;
   if _n_=1 then do;
     declare hash h(dataset: "data2",hashexp:6);
         h.defineKey('a');
         h.defineData('c');
     h.defineDone();
         call missing(a, c);
   end;
   set data1;
   rc=h.find();
   if (rc = 0) then output;
run;

shiyiming

学习了，刚接触Hash表，拜读你的代码。

shiyiming

Of course, you can use hash table for merging without sorting but it's not good idea for large data set.
Because you have to load all data into memory in order to use hash table, so if the data set is exttremely large, it's not applicable.

shiyiming

SQL不是也能实现merge without sort么？