公告

[公告]
2014/01/17
由於已經是faculty的關係,不太有足夠時間寫部落格。因此更新的速度會相當緩慢。再加上近幾年來SAS GLOBAL FORUM沒有出現讓我覺得驚艷的技術文件,所以能分享的文章相對也減少許多。若有人推薦值得分享的SAS技術文件,請利用『問題討論區』告知。

2013/07/19
臉書留言板的功能因為有不明原因故障,因此特此移除。而intensedebate的留言板因管理不易,也一併移除。目前已經開啟內建的 G+ 留言系統,所以請有需要留言的朋友,可直接至『問題討論區』裡面留言。


2007年2月24日 星期六

An Introduction to SQL in SAS®

原文載點:http://www2.sas.com/proceedings/sugi30/257-30.pdf

歷年來有很多人在 SUGI 寫過 PROC SQL 的教學文件,但我覺得這一篇寫的比較好,所以在此推薦給需要的人。這是 Pete Lund 在2005 年 SUGI 30 所發表的一篇頗長的文章。PROC SQL 提供很好的語法讓使用者可以在某個很大的資料庫裡面找到想要使用的資料。其基本的語法有:

• SELECT(選擇變數或欄位名稱)
• FROM (指定資料庫名稱)
• ON (給定搜尋資料所需的條件)
• WHERE (給定搜尋資料所需的「觀測值」條件)
• GROUP BY (分群整合)
• HAVING (整合所需條件)
• ORDER BY (按欄位順序)

最簡單的 PROC SQL 程序可以寫成這樣:

proc sql;
select BookingDate,
ReleaseDate,
ReleaseCode
from SASclass.Bookings;
quit;


簡單的說,就是從 SASclass library 裡面的 Bookings 資料集中挑出 Booking Date, ReleaseDate 和 ReleaseCode 三個變數。

如果想要把上述抓出的變數另存成新的資料集,可用「create table ... as」語法:

proc sql;
create table ReleaseInfo as
select BookingDate,
ReleaseDate,
ReleaseCode
from SASclass.Bookings; quit;


三個變數就會被存入 ReleaseInfo 這個新的資料集裡面。

如果想要對變數做 rename, label, length 和 format 的動作,只要改一下上面那個程式成:

proc sql;
create table ReleaseCodes as
select BookingDate as BD,
ReleaseDate as RD format=monyy7.,
ReleaseCode label='Rel Code'
from SASclass.Bookings; quit;


此時,BookingDate 會被更名為 BD,ReleaseDate 會被更名為 RD,且格式被改為 monyy7.
的格式,ReleaseCode 則為被貼上 Rel Code 的標籤。

如果想要選所有的變數,不需要全部輸入,只要一個星星就可解決:

proc sql;
create table BookingCopy as
select *
from SASclass.Bookings;

quit;


另外,在選擇變數時,可以做一些變數間的簡單數學運算,並將結果存成新的變數,如:

select
BookingDate,

ReleaseDate,
ReleaseDate – BookingDate as LOS
from SASclass.Bookings;


如此程式不但會選出 BookingDate 和 ReleaseDate 兩個變數,還會將兩個相減另存成新變數 LOS。

如果選擇變數時要順便加上一些條件,由於 PROC SQL 程序中並無法使用「If ... then ... else
...」語法,所以要用別的方法取代,其基本型態如下:

CASE
WHEN [condition] THEN [value]
WHEN [condition] THEN [value]
ELSE [value]
END AS [column name]


簡單說就是把「If ... then ... else」改成「When ... then ... else」,最後用「END AS」存出來。有個簡單範例:

select *,
case
when InfractionType eq 'IS' then 'X'

when Severity eq 'S' then 'X'
else ' '
end as CheckThese

from SASclass.Infractions;


這個程式不止選出所有的變數,還另為設了一個新變數「CheckThese」,條件是,當字串變數 InfractionType 等於 IS 還有字串變數 Severity 等於 S 時,就在新變數上填上一個 X,如果沒有符合條件的話就留空白。

下面是一個比較複雜的例子:

case
when InfractionType eq 'IS' and Severity eq 'S' then 1
when InfractionType eq 'IS' then 2
when Severity eq 'S' then 3
else 0
end as CheckThese


如果不同的 when 接的是同樣的變數,那麼程式可以稍微縮寫一下。比方說:

case
when Age lt 13 then 'PreTeen'
when Age lt 20 then 'Teenager'
else 'Old Person'
end
as AgeGroup


兩個 when 後面的變數都是 Age,就可以把他挪到 case 後面,如下所示:

case Age
when lt 13 then 'PreTeen'
when lt 20 then 'Teenager'

else 'Old Person'
end as AgeGroup

如果只是單純地要限制選擇出來的變數符合某些條件,可直接用 where 語法來處理:

select *
from SASclass.Infractions
where Severity eq 'S';


上述程式雖然選擇所有的變數,但只有符合 Severity 變數是 S 的條件才會納入。

select *
from SASclass.Infractions
where Severity eq 'S' and InfractionType eq 'II';


上述程式一次用兩個條件,只要用 and 把兩個條件黏起來即可。

如果只想挑出含有 missing data 的變數,可用 is null 或 is missing 來指定:

select *
from SASClass.Charges
where SentenceDate is null;


如果要選擇某個範圍內的變數,可用 between 語法來指定:

select *
from SASClass.Bookings
where BookingDate
between '1jul2001'd and '30jun2002'd;


上述程式就是只限定 BookingDate 介於 1jul2001 到 30jun2002 這段時間才會輸出。這邊很有彈性的設定是,起點和終點不用限定位置,誰放前誰放後並不會影響結果。

以下要介紹 PROC SQL 針對字串變數所能處理的強大功能。

如果要找一個字串變數裡面含有某些文字的觀測值,可用「contain」這個語法來完成。如:

select *
from SASClass.Charges
where ChargeDesc contains 'THEFT';


這個程式執行後,只要 ChargeDesc 裡面含有 THEFT 這個字的觀測值都會被抓出。因此,諸如“AUTO THEFT”, “THEFT 2”, “PROPERTY THEFT”這些字串全部都會被抓出。如果連大小寫也不管,可以把 upcase() 這個函數套在變數上,如下所示:

where upcase(ChargeDesc) contains 'THEFT'

這樣會連“Car Theft”和“Theft of Property”這些字串也一併挑出來。特別注意的是,contain 語法並不會考慮空格,因次既使 THEFT 這個字跟其他字連在一起(如:“THEFT2” 和“AUTOTHEFT”),也會挑出。

PROC SQL 還可以做模糊比對,使用者不用很明確瞭解要尋找的條件,主要加入「Like」運算子還有一些可供模糊比對的代碼即可。這些代碼有:
  • _ (underscore) 可以模糊比對任何單一字母或數字
  • % (percent sign) 可以模糊比對任意多個字母或數字
以下提供四個範例:

where ChargeDesc like '%THEFT%';

這種用兩個 % 把一個字串包起來的模糊比對方法,跟 contain 的效果一模一樣。

where ChargeDesc like 'THEFT%';

如果只用一個 % 在 THEFT 後面,這只會找出以 THEFT 開頭的字串,如 “THEFT”, “THEFT 2” and “THEFT-AUTO”。

where ChargeDesc like '%THEFT';

如果只用一個 % 在 THEFT 前面,這只會找出以 THEFT 結尾的字串,如“AUTO THEFT” 和 “3RD DEGREE THEFT”。

where ChargeDesc like '%_THEFT';

如果使用 _ 在 % 和 THEFT 之間,則任何數字或字母填進那個空格的 THEFT 字串都會被挑出,即使是空格也包含,因此像“AUTO THEFT” and “AUTO-THEFT” 都會被挑出。如果不加底線,而只留一個空白在 % 和 THEFT 中間的話,那就只會傳回“AUTO THEFT”了。

接著,如果你聽到每個字串,但不知道正確拼法,只要輸入相近的拼音,PROC SQL 也會幫你找出所有可能的結果。這背後的道理涉及到 1918 年 Margaret K. Odell 和 Robert C. Russel 所發明的 Soundex algorithm。PROC SQL 很人性化的用「=*」來代表「sounds like」。

select LastName, FirstName
from SASClass.Inmates
where LastName =* 'Smith';


上述程式是要從 LastName 這個變數中找出聽起來像Smith 的字串,還順便會做個次數統計,輸出如下報表:



This is pretty cool!!!!!

接著,PROC SQL 也提供排序功能。

select *
from SASClass.Charges
where ChargeType eq 'A'
order by FIM;


則上述的結果會以 FIM 遞升的次序來排列。如果要用依序用多個變數排列,就在 order by 後面依序加上,記得用逗號分開。如下所示:

select OrgAgency format=$Agency., BookNum, FIM
from SASclass.Charges
where ChargeType eq 'A'
order by FIM,OrgAgency;


如果要以「遞減」的方式排序,則可在變數後面加上 desc 這個字。

select OrgAgency format=$Agency., BookNum, FIM
from SASclass.Charges
where ChargeType eq 'A'
order by FIM desc,OrgAgency desc;


order by 語法還可以用變數的第幾個字母或字串來排列。假設上述例子要特別指定用 FIM 的第一個字母來排列,則語法如下:

select OrgAgency format=$Agency., BookNum, FIM
from SASclass.Charges
where ChargeType eq 'A'
order by substr(FIM,1,1), OrgAgency;


substr 是 SAS DATA 程序中的一個函數,可以指定從第幾個字母開始割出幾個字母出來,因此 substr(FIM, 1, 1)即表示從 FIM 的第一個字母開始割一個字母出來,亦即選定 FIM 變數的第一個字母。

此外,order by 針對字串變數的預設雖然是依照字母排列,但是會先排完大寫字母,然後才排小寫字母。如果要叫他不要管大小寫,那可以用upper( ) 函數來更改,如:

select ChargeDesc
from MixedCase
order by upper(ChargeDesc);


要特別注意的一點是,upper( ) 和lower( ) 函數是 PROC SQL 專用,若在 SAS 其他的地方使用類似的函數,要改成 upcase( ) 和 lowcase( )。

之前有提到,我們可以在 PROC SQL 裡面做一些小小的運算,然後將運算結果輸出成新的變數。下面列出一些常用的運算函數:

  • Avg:平均數
  • NMiss: missing values 的個數
  • Count:non-missing values 的個數
  • Prt :計算P(T>|t|),T 代表 T 分配。
  • CSS:校正後的sum of squares
  • Range:全距
  • CV:變異係數coefficient of variation
  • Std:標準差standard deviation
  • Freq :同 Count (same as Count)
  • StdErr:標準誤standard error of the mean
  • Max :最大值maximum value
  • Sum:總和sum of values
  • Mean:同 Avg
  • T:T-test 的 T 值
  • Min :最小值
  • USS :無校正的 sum of squares
  • N:同 Count
  • Var:變異數
下面有一些小範例:

select *, sum(GoodTime,CreditDays) as OffDays
from SASClass.Charges;


上述程式除了選擇所有變數外,另外將 GoodTime 和 CreditDays 兩個變數的加總並另存為新變數 OffDays。如圖所示:



當然,PROC SQL 也可以對單一變數內的觀測值進行運算:

select sum(Bail) as TotalBail, mean(Bail) as MeanBail, max(Bail) as MaxBail, nmiss(Bail) as NoBailSet
from SASclass.Charges;


上述程式針對 Bail 這個變數作了四個統計運算,並且分別存成四個新的變數。如圖所示:



PROC SQL 也可以針對類別變數做個別的操作。比方說:

select BookNum, sum(Bail) as TotalBail
from SASClass.Charges
group by BookNum;


這個程式會針對 BookNum 內不同的類別各自做 sum(Bail) 的動作。如圖所示:



PROC SQL 另外提供一個簡單的 group by 方法。如果要 group by 多個變數,可以只填上這些變數在 select 後面的「位置」,這樣 SAS 仍可以準確的抓出你要 group by 的變數。如:

select FIM, ChargeType, mean(Bail) as TotalBail
from SASClass.Charges
group by FIM, ChargeType;


這個程式要以 FIM 和 ChargeType 做 group by 的動作。由於他們在 select 後面的位置是第一個和第二個,因此可以用下面這個方法來取代:

group by 1,2;

接著來介紹一個特殊的運算子「distinct」。他的功能類似整理變數,把一些重複的觀測值剃掉,讓使用者可以清楚的看裡面有多少個分類。舉例來說:

select distinct Facility
from SASClass.Bookings;


如果 Facility 裡面的觀測值是「5, 5, 6, 6」這四個數字的話,則程式僅會列出「5, 6」這兩個數字。(註:我知道這個例子舉的很爛,可是突然天外飛來一筆地想要這樣解釋,這樣讀者應該會比較瞭解 :D 原文中並不是用這個例子,特此說明!)

若結合 count( ) 函數就可以知道這個變數裡面有多少個類別:

select count(distinct Facility)
from SASClass.Bookings;


以「5, 5, 6, 6」這個例子來看的話,輸出結果就是「2」。

特別注意一點,count( ) 函數只能一次針對一個變數,所以放兩個以上的變數會出現 error。如:

select count(distinct Race, Sex)
from SASclass.Inmates;


上面這個程式是不行的。解決的方法是用連結(concatenate)符號「||」把變數連起來,這樣就可以傳回兩個變數組合數目。如果要分別計算次數,那就要用數個 count( ) 才行。

select count(distinct Race||Sex)
from SASclass.Inmates;


說明完運算函數後,要特別聲明一點。經過運算函數所產生出來的新變數,不能直接用在 where 或 when 條件式內。舉例來說:

create table OffDays as
select *, sum(GoodTime,CreditDays) as OffDays
from SASClass.Charges
where OffDays gt 0;


上述程式會發生 SAS 不知道有 OffDays 這個變數存在的問題。解決的方法是在新變數前面加上「calculated」這個字,這樣 SAS 就知道這是經由某個運算函數所產生的新變數:

create table OffDays as
select *, sum(GoodTime,CreditDays) as OffDays
from SASClass.Charges
where calculated OffDays gt 0;


下面範例是 OffDays 出現在 when 條件式後的改法:

select *, sum(GoodTime,CreditDays) as OffDays,
case
when calculated OffDays gt 0 then '*'
else ' '
end as OffDayFlag
from SASClass.Charges;


最後要介紹一個和 where 很像的語法:having。 Where 的功能是可以限制僅列出挑選出來變數的條件。Having 的功能也是一樣,但特別是用在已經做了 group by 這個動作之後。如果沒有使用 group by 在你的 PROC SQL 內,則 where 和 having 的輸出結果會完全一樣。如下面這兩個程式所示:

select Race
from SASclass.Inmates
where Sex eq 'M';

select Race
from SASclass.Inmates
having Sex eq 'M';


那麼來比較一下在使用 group by 情況下,where 和 having 的不同。



左邊的結果顯示 PROC SQL 是先挑出 Sex 是 M 的觀測值,然後再根據不同的 Race 計算 Total。右邊的結果則是先計算不同 Race 的 Total,然後再把 Sex 是 M 的挑出來。因此右邊的 A 個數就是左邊所顯示的 91 個。

基本上我這篇文章省略了一些部分,有興趣的可以去看 Pete 的原文。

CONTACT INFORMATION

Pete Lund
Looking Glass Analytics
215 Legion Way SW
Olympia, WA 98501
(360) 528-8970
(360) 570-7533
pete.lund@lgan.com
www.lgan.com
CODE { display: block; /* fixes a strange ie margin bug */ font-family: Courier New; font-size: 8pt; overflow:auto; background: #f0f0f0 url(http://klcintw.images.googlepages.com/Code_BG.gif) left top repeat-y; border: 1px solid #ccc; padding: 10px 10px 10px 21px; max-height:200px; height:200px; // for IE6 line-height: 1.2em; }