[SAS] 資料合併問題


這個方塊裡面蘊含了__的力量。

資料合併是處理資料時最常見的一種邏輯操作了。這裡的合併指是RDBMS中的「橫向」合併,也就是兩張或以上的表單根據其共同欄位來合體擴充主表單欄位的操作,在SQL中謂之JOIN。Stata雖然也有JOIN為名的command,不過主要操作是以MERGE command來達成。至於SAS,這頭計量軟體界的坑錢巨獸(你沒聽錯坑錢巨獸我再說一次坑錢巨獸),則有意外但又不是很意外非常多不同的方法可以用來操作資料合併。

網路上有許多文章提出整理,也不乏有全面的效能評比,但我發現意外地(真的很意外)竟然沒有一個很一致的共識。好吧,這篇文章也沒有要搞出什麼很有說服力的效能評比的意思。只是想說突然沒事想做所以就來稍微紀錄一下好了。

首先,由於SAS有PROC SQL這個將SQL標準語法納入其框架的程序,所以基本上SQL裡的JOIN方法都可以直接移植到SAS裡來使用。溫馨。不過SAS本身DATA STEP中的MERGE陳述句(Statement)其實已經十分好用了,所以我通常不會特別想開PROC SQL——重點是擺明了這招也沒有比較快。不過因為SQL的邏輯比較廣為人知,所以我還是以他的概念為中心出發。SAS中的MERGE陳述句基本上就是:

data mergedTable;
merge table1 (in = main) table2;
by commonVar;
if main = 1;
run;

其中的by變數就是共同欄位,而IN這個data set option則是用來製造一個布林(在這裡取此布林變數名為main),他將會告訴你任一筆資料是否來自特定表單,藉由這個變數加上if subset的手段就可以進行所謂的LEFT OUTER JOIN——只有在主要表單中出現的紀錄才會被保留——或者任何其他形式的JOIN。如果沒有利用IN選項進行過濾合併結果會是什麼其實我也不記得了哈哈哈。咳,我猜大概會是FULL OUTER JOIN吧!要注意的是,MERGE陳述句並不會更新資料,如果有更新的意圖則改用同型的UPDATE陳述句即可。

在Stata裡,MERGE command基本上是萬用的合併程序了,在11版之後進一步追加了mapping option之後幾乎無所不能,還可以做到SQL JOIN概念中不存在對應的合併邏輯,不過這篇畢竟是TAG了SAS,我就還是不要再表現出明顯對Stata的鍾愛好惹。

回到SAS,MERGE陳述句不比PROC SQL難用,因為它是DATA STEP的框架,很多data set option可以採用,所以像是限定表單的一部份這種操作也能很輕易完成。不過它確實還是有一個惱人的缺點,就是 by變數必須先sort,當資料表很巨大的時候這會花上不少準備時間,而PROC SQL不必經過這道手續。

不過!有一招在運算層級基本概念上完全迥異於MERGE陳述句(以及PROC SQL)的手段不得不學起來,那就是HASH物件。HASH物件是SAS裡我覺得很神奇的一個東西,反映了SAS意圖包山包海東拼西湊的人工演化跡象(?)。什麼是HASH物件?它是一個可以並且只可以在DATA STEP中宣告並操作的物件。當我用了「物件」這個字眼,不用懷疑,我說的就是OO的那個OBJECT。

HASH物件的應用很多,我也還沒有全般參透(我才不會承認我是上個禮拜才知道有這個東西的!),不過任何討論SAS的資料合併問題的文獻,都不可能不提及使用HASH來進行合併。

在使用HASH物件進行合併時,我們是將要合併的表單直接丟進這個物件,換言之,這個作法是一個memory-based的操作,比起DATA STEP的框架中SAS原則上一次就只看見一筆資料,HASH物件則耗用了足夠的記憶體空間來將整個表單給全吞下肚裡,這是善用記憶體閒置的優勢。(希望你的電腦有這個優勢。)

HASH物件比較麻煩的地方則是語法冗餘了點,而且我還沒找到一個可以很有彈性地宣告很多變數進去的手段。所以它適合用在你要合併的資料表很大、但欄位少(少到你一個一個打出來不會嫌麻煩的程度)的場合。速度基本上遠超過前面兩招(記憶體不足的走開),而且,也不需要天殺的sort。順帶一提Stata 11之後的MERGE指令已經會自動幫你sort了SWEET!

在進行合併時,應用HASH物件的推薦語法是:

data MergeTable;
if 0 then set Table1 Table2 (keep = commonVar otherVar);
if _n_ = 1 then do;
declare hash table2(dataset: "table2(data set option)");
table2.DefineKey("commonVar");
table2.DefineData("commonVar", "otherVar");
table2.DefineDone();
end;
set table1;
if table2.Find = 0 then output;
run;

其中if 0 then set是一個好用的小技巧,目的是將表單的結構讀進去但不花費時間去讀取任何一筆紀錄,如此一來在這個DATA STEP中我們就擁有了操作所涉及變數的必要資訊而不必另外設法創建(想像SQL SERVER中麻煩的TABLE SCHEMA吧),然後我們限定在DATA STEP的第一次疊代中進行HASH物件的宣告,並且定義資料主鍵與其他欄位,注意這裡既然使用了主鍵(KEY)這個字眼,就代表它必須有唯一性,但共通欄位不具唯一性仍然可以處理,只是需要額外的一些coding在這裡就不深入了。

HASH物件中的Find這個方法(完完全全就是OOP-Style有沒有!)會傳回一個值,當等於零的時候代表它在當前資料表中找到了與自己的主鍵相符的欄位值,當這件事情發生的時候我們用一個output動作來將資料輸出。

這個方法的速度在處理大型表單(上百萬、千萬的紀錄與十幾個欄位)時十分驚人,令人激賞。

HASH物件也可以用來進行萬惡的sort,或者資料表的更新,和一大堆我還沒研究過的神祕功能,總之,算是個SAS裡面的好用工具,不用花錢買license真是太幸福了。

我對SAS最大的怨念其實是它沒有開放矩陣語言的運算環境,例如Stata的MATA那樣,所以有些演算法很難自己實作。不過他可不是真的沒有,那個東西叫做SAS/IML,貌似十分強大,強大到還可以直接在裡面寫R的語言並叫用R,互丟結果,這麼好的東西,出現在SAS裡面,你就知道你在公司一定用不到啦!

因為它要另外花錢買。(眼神死)

不然其實還蠻想試試看的。老實說,R太強了,只從計量的技術面或學術面來看,市面上根本沒有任何商業計量軟體可以與之匹敵吧。SAS這招叫用R的手段確實高明,不過弔詭的是你得先花錢買license才能在它的產品底下用這個免費的R。(這個世界爛透了。)

0 comment(s):

Post a Comment

回應文章前請注意下列三勿原則:

1)勿拍照;(→會有靈異的照片從你的相機裡跑出來...
2)勿餵食;(→會有飢渴的猛獸從我的網誌裡跑出來...
3)勿告白。(→會有奇怪的東西從站長體內裡跑出來...

謝謝大家的配合。
( > ー <)b