InCarl: SAS PROC SQL入門 (Part B)

這篇為延續2011年所寫的SAS PROC SQL入門 (Part A)小文章（謎之聲：拖搞也拖太久了！！）

本篇主要介紹如何透過SQL建立資料檔，及如何做水平合併資料，同時會提供SQL語法及傳統Data step的語法，或許這樣對學習SQL會比較有幫助。

先建立兩個練習的檔案，分別為ER1和DD1兩檔案，於【程式1】

【程式1】

data er1;
informat er_id $2. funcday yymmdd10. icd $3.;
format funcday yymmdd10.;
input er_id funcday icd;
datalines;
M1 07/01/03 423
M2 07/09/20 478
F3 07/12/15 423
F4 08/01/06 421
;
run;

data dd1;
informat dd_id $2. inday outday yymmdd10. icd $3.;
format inday outday yymmdd10.;
input dd_id inday outday icd;
datalines;
M1 07/01/03 07/01/20 423
M1 07/02/23 07/03/01 414
M3 07/03/04 07/05/01 413
M2 07/09/23 07/09/26 478
F3 07/12/15 07/12/26 423
F3 08/06/09 08/07/06 V4
F3 08/12/13 08/12/17 435
;
run;

建立資料檔：
在SQL中建立資料檔主要是使用「Create Table 資料檔名 AS」這個指令。

【程式2】

proc sql;
    create table create1A as
    select er_id label='醫師ID',
                funcday label='就診日'
    from er1
    where er_id in ('M1', 'F4')
    ;
quit;

data create1B;
    set er1;
    where er_id in ('M1', 'F4');
    label er_id='醫師ID' funcday='就診日';
    keep er_id funcday;
run;

《圖01》

水平合併資料檔：

這一部份在Data step的語法是適合用在一對多、多對一或一對一的合併上，意思就是在合併時候的關鍵欄位，其中一個資料檔的關鍵欄位資料內容為不重複才適合，如果關鍵欄位是多對多，結果會不能預期，也不建議使用。

主要語法為在FROM後使用下列的語法，實際用法請參考範例說明。

FROM 資料表名稱 INNER JOIN | LEFT JOIN | RIGHT JOIN | FULL JOIN 資料表民稱 ON 關鍵變項

1.Inner Join

由文式圖比較容易瞭解Inner Join就是指兩邊關鍵變項都有的才保留。

【程式3】

proc sql;
    create table joint1a as
    select a.er_id as id, a.funcday, b.inday, b.outday
    from er1 as a inner join dd1 as b
    on a.er_id=b.dd_id;
quit;

proc sort data=er1; by er_id;
proc sort data=dd1; by dd_id;
run;

data joint1b;
    merge er1 (in=a) dd1 (rename=(dd_id=er_id) in=b);
    by er_id;
    if a=1 and b=1;
    keep er_id funcday inday outday;
run;

《圖02》

2.Left Join

Left Join所指的意思就是以左邊為主，右邊只保留能與左邊比對到關鍵變項的資料。

【程式4】

proc sql;
    create table joint2b as
    select a.er_id as id, a.funcday, b.inday, b.outday
    from er1 as a left join dd1 as b
    on a.er_id=b.dd_id;
quit;

〔排序後〕
data joint2b;
    merge er1 (in=a) dd1 (rename=(dd_id=er_id) in=b);
    by er_id;
    if a=1;
    keep er_id funcday inday outday;
run;

《圖03》

3.Right Join

Right Join所指的意思就是以右邊為主，左邊只保留能與右邊比對到關鍵變項的資料。，結果於《圖04》。這時要注意將保留的ID設為右邊資料檔的欄位，否則會出現較不合理的結果《圖05》。

【程式5】

proc sql;
    create table joint3b as
    select b.dd_id as id, a.funcday, b.inday, b.outday
    from er1 as a right join dd1 as b
    on a.er_id=b.dd_id;
quit;

〔排序後〕
data joint3b;
    merge er1 (in=a) dd1 (rename=(dd_id=er_id) in=b);
    by er_id;
    if b=1;
    keep er_id funcday inday outday;
run;

《圖04》

《圖05 不合理的結果》

3.Full Join

Full Join依據，結果為將兩張資料表關鍵變項能比對的資料保留，無法比對的資料也會保留下來。如果沒有使用特別的函數，結果於《圖06》，與Data Step的結果相比，在第4筆的資料ID為空白，這是因為以SQL輸出時，是以dd_id作為ID的資料，因此才有如此結果，如要將SQL和Data Step結果一致，需要使用coalesce函數。用法為 coalesce(欄位名稱1,欄位名稱2,…) ，此函數功能為保留每個欄位，沒有遺漏值的第一個值的結果，參考【程式7】。

【程式6】

proc sql;
    create table joint4a as
    select b.dd_id as id, a.funcday, b.inday, b.outday
    from er1 as a full join dd1 as b
    on a.er_id=b.dd_id;
quit;

〔排序後〕
data joint4b;
    merge er1 (in=a) dd1 (rename=(dd_id=er_id) in=b);
    by er_id;
   ;
    keep er_id funcday inday outday;
run;

《圖06》

《圖07》

【程式7】

proc sql;
    create table joint4a1 as
    select coalesce (a.er_id, b.dd_id) as id, a.funcday, b.inday, b.outday
    from er1 as a full join dd1 as b
    on a.er_id=b.dd_id;
quit;

比較SQL Joins 和 Data Step Match-Merges：

這兩個語法我會看心情使用，終究寫Data Step還是比較順，但有時候（主要是要爭取效率）還是會採用SQL的語法，特別在面對巨量數據(Big Data)的時候。

我用兩個模擬個資料，讓各位瞭解SQL其在執行時間上面的優勢為何。
這兩份模擬資料，其資料筆數為500萬並包含3個數值變項，檔案容量約1.1GB。合併後的檔案容量約為1.9GB。

【程式8】

〔建立資料檔〕
data loop1;
do id=50000000 to 1 by -1;
x1=uniform(546);
x2=uniform(546);
output;
end;
run;

data loop2;
do id=50000000 to 1 by -1;
x3=uniform(546);
x4=uniform(546);
output;
end;
run;

〔測試程式〕
proc sql;
create table loop1_2a as
select a.*, b.x3, b.x4
from loop1 as a inner join loop2 as b
on a.id=b.id
;
quit;

proc sort data=loop1 out=loop1a ; by id;
proc sort data=loop2 out=loop2a; by id;
run;

data loop1_2b;
merge loop1a (in=a) loop2a (in=b);
by id;
if a=1 and b=1;
run;

測試結果：

Data Step Match-Merges：3分53秒
SORT1：1分56秒
SORT2：1分12秒
Merge ：45秒

PROC SQL：2分20秒

以上測試結果，SQL明顯快了許多。

不過在實務經驗中，在某些資料大小和電腦環境，Data Step Match-Merges也是有可能會更快的喔，所以建議要針對環境作個簡單的測試。

如果您覺得這篇文章對您有幫助，文章旁的廣告您也剛好有興趣，請幫忙點擊廣告，讓我多一點寫下去的動力。感謝您的幫忙！

系列文章：

如有誤請指教。轉載請註明來源。

InCarl

2013年8月13日

SAS PROC SQL入門 (Part B)

1 則留言:

瀏覽量

授權條款