proc sql

An Introduction to SAS PROC SQL

實驗組與對照組一對一或一對多簡單配對

An Introduction to SAS PROC SQL

原文出處:http://www2.sas.com/proceedings/sugi27/p070-27.pdf

此文為SAS PROC SQL的簡單介紹 可以當作入門學習的文章


1.Creating output and new tables
2.Duplicates and sorting
3.Sub-setting and Caculating
4.Joining Tables
5.Proc SQL and SAS Macro language
6.Editing Observations
7.Data Table Management


1.Creating output and new tables

當我們要對某資料做搜尋時甚至做處理的動作可利用SQL語法來達到
以下為資料king做SQL的選取
其中沒有針對特定變項時的作法如下

data king;
input OBS PATIENT DATE date7. PULSE TEMP BPS BPD ;
cards;
1 101 25MAY01 72 98.5 130 88
2 101 01JUN01 75 98.6 133 92
3 101 08JUN01 74 98.5 136 90
4 102 30JUL01 81 99.0 141 93
5 102 06AUG01 77 98.7 144 97
6 102 13AUG01 78 98.7 142 93
7 103 24JUL01 77 98.3 137 79
8 103 31JUL01 77 98.5 133 74
9 103 07AUG01 78 98.6 140 80
10 103 14AUG01 75 99.2 147 89
11 104 22AUG01 72 98.8 128 83
12 104 29AUG01 69 99.1 131 86
13 104 05SEP01 71 98.9 127 82
;
run;

PROC SQL;
SELECT *
FROM king;
QUIT;



SAS SQL語法是以
PROC SQL;開始
QUIT;做結束

資料搜尋如說故事一樣 先打上SELECT
並且選擇要找的變相，如上則是選擇所有變相所以以*號為標記
若要選擇特定變項時 只要列出各變相 並且以逗號做區隔
如SELECT PATIENT, DATE, BPS, BPD

接著要搜尋的原資料來源宣告為FROM king;
就能夠成簡單的SQL搜尋語法

並且注意SELECT到FROM 都沒有用到;號做結束
只有在FROM後面才做結束的動作

其SQL搜尋結果如下



此外上述的動作僅在秀出報表(類似proc print的動作)
若要把搜尋的結果以新的資料型態出現時可以改為
PROC SQL;
CREATE TABLE kingbee AS
SELECT PATIENT, DATE, BPS, BPD
FROM king;
QUIT;

其中 CREATE TABLE AS 為固定用語
SQL搜尋後將資料轉為kingbee檔
其結果如下


上述中若資料的變相多時會有搜尋上的麻煩
如100個變相要選取便要在SELECT那邊打上100個變相名稱
這在語法上會有累人的地方
故有keep drop語法可以供使用,只要在from那邊做宣告
其方法如下
PROC SQL;
CREATE TABLE kingbee AS
SELECT *
FROM king (DROP=TEMP);
QUIT;

其結果如下



2.Duplicates and sorting

當在使用SQL語法選取特定變項時有時會希望知道該特定變項由哪些數值所構成
這時可以在欲選取的變項前額外宣告 DISTINCT，語法如下


PROC SQL;
SELECT DISTINCT PATIENT
FROM king;
QUIT;


這時只會顯示PATIENT變項內所構成的數值有哪些，其結果如下




若對於選取資料欲做排序的動作可以加入order by，並且可
在選取變項後，針對特定變項使用descending(降冪)或ascending(升冪)來配合排序，語法如下

PROC SQL;
SELECT PATIENT, DATE
FROM king
ORDER BY PATIENT descending, DATE ;
QUIT;


其結果如下



3.Sub-setting and Caculating
當選取的變項欲作額外計算或是修改其屬性時
可以在選取時接在後方 或是 在後方 宣告
此外對於PATIENT選取時只選出其值為101、103的資料
其語法如下

PROC SQL;
CREATE TABLE kingbee AS
SELECT PATIENT, DATE FORMAT=DATE7., BPS,
BPD, (TEMP-32)/9*5 AS TEMPC
FROM king
WHERE PATIENT IN (101 103)
ORDER BY PATIENT, DATE DESCENDING;
QUIT;


其中 Date 格式轉為DATE7.

將(TEMP-32)/9*5計算而成為TEMPC變項，中間以as(顧名思義：如同為)做宣告

透過where的宣告將PATIENT變項中含101及103之觀察值列出
其語法中WHERE PATIENT IN (101 103)，in 的用法可變化為
where PATIENT=101 or PATIENT=103;

其結果如下



倘若在格式上的設定也可以額外改寫不同表達方式
如原來的DATE FORMAT=DATE7，可以改用put來宣告
如PUT(DATE,DATE7.) AS CDATE LENGTH=7

其語法如下
PROC SQL;
CREATE TABLE kingbee AS
SELECT PATIENT, PUT(DATE,DATE7.) AS CDATE LENGTH=7, BPS,
BPD, (TEMP-32)/9*5 AS TEMPC
FROM king
WHERE PATIENT IN (101 103)
ORDER BY PATIENT, DATE DESCENDING;
QUIT;

其中 put(DATE,DATE7.) 是將DATE變相轉換為DATE7.的格式
而LENGTH=7則是宣告新的變項CDATE在轉換格式後的長度

其結果如下




另外對於SQL也可以做一些搜尋過程的計算
如求出資料有多少筆數、平均值、變更資料的進位問題.....等等諸如此類的
其語法如下

PROC SQL;
SELECT
COUNT(*) AS N,
ROUND(MEAN(BPS),0.01) FORMAT=6.2 AS BPSMEAN,
ROUND(MEAN(BPD),0.01) FORMAT=6.2 AS BPDMEAN
FROM king;
QUIT;


其中COUNT(*) AS N 則是求出觀察值有幾筆

ROUND(MEAN(BPS),0.01)是先以MEAN()函數求出BPS的平均值
再以ROUND()函數宣告該平均值以小數點兩位作四捨五入的進位

其結果如下



此外SQL的語法是具有彈性的
所以當有特別針對某變項中的特定值作額外計算時也可以利用where配合
如下

PROC SQL;
SELECT
COUNT(*) AS N,
ROUND(MEAN(BPS),1) FORMAT=6.2 AS BPSMEAN,
ROUND(MEAN(BPD),1) FORMAT=6.2 AS BPDMEAN
FROM king
WHERE PATIENT IN (101 103);
QUIT;

因為針對PATIENT的101及103的結果做選取
所以樣本數N則為7筆，並且平均值也跟著不同


而在SQL的語法中
若要針對群組(GROUP)分類時
可以利用GROUB by語法
如下

PROC SQL;
SELECT PATIENT,
COUNT(PATIENT) AS N,
DATE,
BPD,
MAX(BPD) AS BPDHIGH,
ROUND(BPD/(CALCULATED BPDHIGH)*100, 0.01) AS BPDPCT
FROM king
GROUP BY PATIENT
;
QUIT;

GORUP BY用法須與全域宣告語法作配合方能顯示出其實用性
如GROUP BY PATIENT是宣告針對四個族群101、102、103、104來探討後續計算的動作，而用途
如COUNT(PATIENT) AS N是用來計數觀察值，若有GROUP BY 做配合時
則只會計算依據PATIENT分類(101、102、103、104)中的觀察值

如MAX(BPD) AS BPDHIGH則是取BPD變相中最大值，若直接使用時僅會挑處整個資料的最大值97
若有GROUP BY 做配合時則只會計算依據PATIENT分類(101、102、103、104)中的觀察值選出各自的最大值(92、97、89、86)
而後續的計算BPDPCT則是引用到group by時各自挑出的最大值，所以必須以CALCULATED做BPDHIGH引用的宣告

其結果如下


而下列語法中另使用HAVING宣告其顧名思義就是在透過群族後保留特定意義的語法

PROC SQL;
SELECT PATIENT,
COUNT(PATIENT) AS N,
DATE,
BPD,
MAX(BPD) AS BPDHIGH,
ROUND(BPD/(CALCULATED BPDHIGH)*100, 0.01) AS BPDPCT
FROM king
GROUP BY PATIENT
HAVING BPD = CALCULATED BPDHIGH
;
QUIT;

其語法則是只保留BPD變相與挑選出最大值BPDHIGH變相相等之觀察值
其結果如下



若需要針對特定群族做修改或計算時
可透過CASE的宣告方式來完成
其用法很像IF THEN
其語法為

CASE 條件項或目標變項
WHERE 條件值1 THEN 結果值1
WHERE 條件值2 THEN 結果值2
....
ELSE 上述條件不符時的其餘結果值
END AS 處理後的變項名稱

其語法如下

PROC SQL;
SELECT PATIENT,
CASE PATIENT
WHEN 101 THEN 'A'
WHEN 102 THEN 'B'
WHEN 102 THEN 'B'
ELSE 'D'
END AS GP "群組"
FROM king
;
QUIT;


其結果如下






4.Joining Tables


如今有一個新資料想要併入原來的資料，有如DATA步驟時的MERGE語法
原資料與新資料如下

data king;
input OBS PATIENT DATE date7. PULSE TEMP BPS BPD ;
cards;
1 101 25MAY01 72 98.5 130 88
2 101 01JUN01 75 98.6 133 92
3 101 08JUN01 74 98.5 136 90
4 102 30JUL01 81 99.0 141 93
5 102 06AUG01 77 98.7 144 97
6 102 13AUG01 78 98.7 142 93
7 103 24JUL01 77 98.3 137 79
8 103 31JUL01 77 98.5 133 74
9 103 07AUG01 78 98.6 140 80
10 103 14AUG01 75 99.2 147 89
11 104 22AUG01 72 98.8 128 83
12 104 29AUG01 69 99.1 131 86
13 104 05SEP01 71 98.9 127 82
;
run;
data king2;
input OBS PATIENT DATE : date7. MED $ 5. DOSES AMT UNIT $ ;
cards ;
1 102 30JUL01 Med A 2 1.2 mg
2 102 06AUG01 Med A 3 1.0 mg
3 102 13AUG01 Med A 2 1.2 mg
4 103 24JUL01 Med B 3 3.5 mg
5 103 31JUL01 Med B 3 3.5 mg
6 103 08AUG01 Med B 3 3.5 mg
7 104 22AUG01 Med A 2 1.5 mg
8 104 29AUG01 Med A 2 1.5 mg
9 104 05SEP01 Med A 2 1.5 mg
10 105 18JUN01 Med B 1 4.5 mg
11 105 25JUN01 Med B 2 3.0 mg
12 105 02JUL01 Med B 1 5.0 mg
;
run;



其中欲將原資料king檔與king2檔作PATIENT與DATE變相的配對
則可以利用 join語法
其與法如下

PROC SQL;
SELECT

king.PATIENT, king.DATE AS DATE, king.PULSE ,

king2.MED, king2.DOSES, king2.AMT

FROM king inner JOIN king2
on (king.PATIENT = king2.PATIENT) and (king.DATE = king2.DATE)
;
QUIT;


此處語法因使用king與king2兩資料時則須在select使用變項時註明原資料檔名
而在合併時須打上
檔案1 inner join 檔案2
on 合併條件1 and 合併條件2

其中inner為合併的方式，另有left、right、full，其合併後會依據宣告做合併的方向
如left join，則是檔案1<檔案2
right join，檔案1>檔案2
full join，則是整體合併，沒合併到也能看到

其結果如下



5.Proc SQL and SAS Macro language

以下需具有巨集語法的經驗方能運用自如：
SQL另有提供將搜尋後的結果納入巨集語法MARCO中
其方式就是使用INTO的方法
語法如下

data king;
input OBS PATIENT DATE date7. PULSE TEMP BPS BPD ;
cards;
1 101 25MAY01 72 98.5 130 88
2 101 01JUN01 75 98.6 133 92
3 101 08JUN01 74 98.5 136 90
4 102 30JUL01 81 99.0 141 93
5 102 06AUG01 77 98.7 144 97
6 102 13AUG01 78 98.7 142 93
7 103 24JUL01 77 98.3 137 79
8 103 31JUL01 77 98.5 133 74
9 103 07AUG01 78 98.6 140 80
10 103 14AUG01 75 99.2 147 89
11 104 22AUG01 72 98.8 128 83
12 104 29AUG01 69 99.1 131 86
13 104 05SEP01 71 98.9 127 82
;
run;



PROC SQL;
SELECT
patient into: p1 separated by ','
FROM king
;
QUIT;

%put &p1 ;


上述語法中，欲將patient變相所抓取的資料放入MARCO語法
故 鍵入 INTO 宣告 並 使用冒號:
並接上巨集變數P1
而在巨集使用時宣告%put &p1(此用法僅為範例，尚有更多巨集用途自行開發)
把抓取的patient值轉為巨集指令 而separated by ','僅是轉換時給予頓號(可加可不加)
但整體來說僅一個字串
"101,101,101,102,102,102,103,103,103,103,104,104,104"變為巨集語言
而此範例可以在log視窗處看到結果
如下





然而上述作法僅有將一個字串轉換為巨集語言
若要將同個變相轉為多個巨集語言時可改為如下

PROC SQL;
SELECT
distinct patient into:p1 - :p4
FROM king
;
QUIT;
%put &p1 &p2 &p3 &p4 ;


上述distinct僅是宣告patient的值有哪幾類予以顯示出

而上述中 :p1 - :p4
則是將patient的值轉為四個巨集語言P1 p2 p3 p4
也可以改寫為 :p1 :p2 :p3 :p4
只是當宣告量一多時則用 - 來概括
其結果如下


當然對於多個變相要做轉換巨集語言除了重複上述動作外，仍可一次寫完
其語法如下

PROC SQL;
SELECT
bps , bpd into: s1- :s10 , :q1-:q10
FROM king
;
QUIT;
%put &s6 &s9 &q3 &q10;


中途僅用逗號做區隔即可
如S1-S10 與 q1-q10 各代表bps與bpd前十個資料
如取 s6 s9 q3 q10
其結果如下




6.Editing Observations
SQL語法允許過程中新增或刪減觀察值

若要新增觀察值時可利用INSERT INTO，並配合VALUES或SET，做新增觀察值的方式
語法如下

data king;
input OBS PATIENT DATE date7. PULSE TEMP BPS BPD ;
cards;
1 101 25MAY01 72 98.5 130 88
2 101 01JUN01 75 98.6 133 92
3 101 08JUN01 74 98.5 136 90
4 102 30JUL01 81 99.0 141 93
5 102 06AUG01 77 98.7 144 97
6 102 13AUG01 78 98.7 142 93
7 103 24JUL01 77 98.3 137 79
8 103 31JUL01 77 98.5 133 74
9 103 07AUG01 78 98.6 140 80
10 103 14AUG01 75 99.2 147 89
11 104 22AUG01 72 98.8 128 83
12 104 29AUG01 69 99.1 131 86
13 104 05SEP01 71 98.9 127 82
;
run;
PROC SQL;
INSERT INTO king
VALUES(14 , 102, '20AUG2001'd , 75, 98.4, 122, 90);
QUIT;

上述語法中 INSERT INTO則是指定要新增的觀察值放入king資料檔中
VALUES則是將新增的觀察值其值依位置次序放入，也就是不管變相為何，在原始資料king中會先放入14再放入102....至122、90去填補各變相位置

結果如下圖



然而對於依位置次序放入有時會有不符合需求，故以SET的方式來表現
語法如下

PROC SQL;
INSERT INTO king
SET PATIENT=106, DATE='27AUG2001'd, PULSE=77, TEMP=98.8, BPS=129, BPD=88 ;
QUIT;


其用法則式以SET宣告，並指定各變相值為如何
其結果如下



若要針對某個觀察值或符合某條件的觀察值予以刪除時可以使用DELETE語法，其語法如下

PROC SQL;
delete from king
WHERE PATIENT = 101;
QUIT;

此DELETE須配合where等條件式，若未使用則觀察值會被刪光光，是不得不注意的
其結果如下



此外對於某個觀察值或符合某條件的觀察值欲以更正或是更新，可利用UPDATE，並以SET與WHERE來配合更改資料，
其語法如下

PROC SQL;
UPDATE king
set BPD=BPD*2
where PATIENT=103;
quit;

上述語法則是更新原始資料king內符合(where)PATIENT=103，則將BPD給予兩倍
其結果如下



7.Data Table Management

SQL語法中有幾種主要語言其中select，另有上述介紹過的INSERT INTO等
而再接下來說明的則是另一種對於資料表格內變相做修飾的方法ALTER TABLE，如同字面上的翻譯就是修改表格
而在使用了ALTER TABLE關鍵字後可以針對資料內的變相做長度變更、型態變更、輸出型態變更、標籤變更或剃除某個變相等，其語法如下

data king;
input OBS PATIENT DATE date7. PULSE TEMP BPS BPD ;
cards;
1 101 25MAY01 72 98.5 130 88
2 101 01JUN01 75 98.6 133 92
3 101 08JUN01 74 98.5 136 90
4 102 30JUL01 81 99.0 141 93
5 102 06AUG01 77 98.7 144 97
6 102 13AUG01 78 98.7 142 93
7 103 24JUL01 77 98.3 137 79
8 103 31JUL01 77 98.5 133 74
9 103 07AUG01 78 98.6 140 80
10 103 14AUG01 75 99.2 147 89
11 104 22AUG01 72 98.8 128 83
12 104 29AUG01 69 99.1 131 86
13 104 05SEP01 71 98.9 127 82
;
run;


PROC SQL ;
ALTER TABLE king
add kkk num
MODIFY DATE FORMAT=MMDDYY8.
DROP TEMP
;

UPDATE king
set kkk=BPD+BPS
;

select * , BPD+BPS as nnn
from king ;
QUIT;


上述語法中
宣告ALTER TABLE來修飾king資料表

add則是宣告新增變項kkk，並且資料型態為數值型(num)[文字型可改char]，

MODIFY則是宣告修飾已存在的變項，DATE的格式改為FORMAT=MMDDYY8.

DROP則是宣告拋棄TEMP變項

而上述這些語法可以配合其他用法如ALTER TABLE新增變項再利用UPDATE將新增的變項導入新值

另外新增變項也可以在SELECT部分實現，如上 BPD+BPS as nnn

其結果如下