SAS中的SQL語句完全教程(20210313123437)

1、SAS 中的 SQL 語句完全教程之一： SQL 簡介與基本查詢功能 本系列全部內(nèi)容主要以 SQL Processing with the SAS System (Course Notes) 為主進行講解， 本書是在網(wǎng)上下載下來的， 但忘了是在哪個網(wǎng)上下的， 故不能提供下載鏈接了， 需要的話可 以發(fā)郵件向我索取， 我定期郵給大家， 最后聲明一下所有資料僅用于學習， 不得用于商業(yè)目 的，否則后果自負。1 SQL 過程步介紹1.1 SQL 過程步可以實現(xiàn)下列功能：查詢 SAS 數(shù)據(jù)集、從 SAS 數(shù)據(jù)集中生成報表、 以不同方式實現(xiàn)數(shù)據(jù)集合并、 創(chuàng)建或刪除 SAS 數(shù)據(jù)集、視圖、索引等、更新已存在的

2、數(shù)據(jù)集、使得SAS 系統(tǒng)可以使用 SQL 語句、可以和SAS 的數(shù)據(jù)步進行替換使用。注意， SQL 過程步并不是用來代替 SAS 數(shù)據(jù)步，也不是一個 客戶化的報表工具，而是數(shù)據(jù)處理用到的查詢工具。1.2 SQL 過程步的特征SQL 過程步并不需要對每一個查詢進行重復、每條語句都是單獨處理、不需要 print 過程步 就能打印出查詢結(jié)果、 也不用 sort 過程步進行排序、 不需要 run 、要 quit 來結(jié)束 SQL 過程步1.3 SQL 過程步語句SELECT ：查詢數(shù)據(jù)表中的數(shù)據(jù)ALTER ：增加、刪除或修改數(shù)據(jù)表的列CREATE ：創(chuàng)建一個數(shù)據(jù)表DELETE ：刪除數(shù)據(jù)表中的列DESC

3、RIBE ：列出數(shù)據(jù)表的屬性DROP :刪除數(shù)據(jù)表、視圖或索引INSERT ：對數(shù)據(jù)表插入數(shù)據(jù)RESET :沒用過，不知道什么意思SELECT :選擇列進行打印UPDATE :對已存在的數(shù)據(jù)集的列的值進行修改2 SQL 基本查詢功能2.1 SELECT 語句基本語法介紹SELECT object-item FROM from-listGROUP BY group-by-item ORDER BY order-by-item ; 這里 SELECT ：指定被選擇的列FROM ：指定被查詢的表名WHERE ：子數(shù)據(jù)集的條件GROUP BY ：將數(shù)據(jù)集通過 group 進行分類HAVING ：根據(jù)

4、GROUP BY 的變量得到數(shù)據(jù)子集ORDER BY ：對數(shù)據(jù)集進行排序2.2 SELECT 語句的特征 選擇滿足條件的數(shù)據(jù)、數(shù)據(jù)分組、對數(shù)據(jù)進行排序、對數(shù)據(jù)指定格式、一次最多查詢 32 個 表。這里還要提到的就是，在 SAS 系統(tǒng)中，對于表名和變量名一般不超過32 個字符，對于庫名，文件引用名，格式等不能超過8 個字符2.3 Validate 關(guān)鍵字Validate 關(guān)鍵字只存在于 select 語句中、可以在不運行查詢的情況下測試語句的語法、檢查 列名是否合法、對于不正確的查詢將打印其消息。例：1 proc sql;2 validate3 select Region, Product,Sa

5、les4 from sashelp.shoes5 where Region = Africa;NOTE: PROC SQL 語句有有效語法。6 quit; 此外，我們還可以用 noexec 選項也可以用來進行語法測試。例：7 proc sql noexec;8 select Region, Product,Sales9 from sashelp.shoes10 where Region = Africa;NOTE: 由于 NOEXEC 選項，未執(zhí)行語句。11 quit;這里提示未執(zhí)行，未提示錯誤，說明該語句沒有語法錯誤。但是如果加入一個 sashelp.shoes 表里沒有字段，這里就會出現(xiàn)錯

6、誤，例：12 proc sql noexec;13 select Region, Product,Sales,test14 from sashelp.shoes15 where Region = Africa;ERROR: 以下這些列在起作用的表中沒有找到 : test.16 quit;2.4 查詢列我們可以像 2.3 那樣查詢指定列，也可以用* 來查詢所有列。例：proc sql;select *from sashelp.shoes;quit;這里我們可以用 feedback 選項來查看到底我們選擇了哪些列：17 proc sql feedback;18 select *19 from sa

7、shelp.shoes;NOTE: Statement transforms to:select SHOES.Region, SHOES.Product, SHOES.Subsidiary, SHOES.Stores, SHOES.Sales, SHOES.Inventory, SHOES.Returnsfrom SASHELP.SHOES;20 quit;這時，我們可以看到從sashelp.shoes表中選擇了 8個列2.5 消除重復值我們可以用 distinct 選項來消除重復值。例如，我們要得到?jīng)]有重復的所有地區(qū)的名稱：proc sql;select distinct Regionfro

8、m sashelp.shoesquit;2.6 where 子集查詢2.6.1 比較運算符先列出where 語句用到的比較運算符LT 大于EQ =等于LE = 大于或等于NE A= 不等于例如，我們要查詢 sales 大于 100000 的所有數(shù)據(jù)： proc sql;select *from sashelp.shoeswhere sales100000 ;quit ;262 in :只要滿足in里的任意一個值，表達式即為真，例如，我們要選擇 和 Eastern Europe 的所有數(shù)據(jù)：proc sql;select *from sashelp.shoeswhere Region in (A

9、frica,Eastern Europe);quit ;2.6.3 邏輯運算符OR | 或AND &是NOT A非例如，選擇 Region在Africa和Eastern Europe，且銷售額大于 100000 proc sql;select *from sashelp.shoeswhere Region in (Africa,Eastern Europe) and sales 100000;quit ;2.6.4 CONTAINS 或?:判斷某列是否包含指定字符串例如，選擇列 Region包含Afr的數(shù)據(jù)：proc sql;select *from sashelp.shoeswhere Re

10、gion ? Afr;Region 在 Africa的所有數(shù)據(jù)：quit ;2.6.5 IS NULL 或 IS MISSING ：判斷某列數(shù)據(jù)是否為空例如，如果找出 Region 為空的數(shù)據(jù)：proc sql;select *from sashelp.shoeswhere Region is missing;quit ;注意， 這里我們還可以用以下表達式對 where 語句進行替換。 如果 region 為數(shù)值型變量， 則 可以用 region=. ，如果 region 為字符型變量，則可以用 region= 進行替換。2.6.6 Between and ：選擇某一區(qū)間的數(shù)據(jù)例如選擇sale

11、s大于100000,但小于200000的所有數(shù)據(jù)：proc sql;select *from sashelp.shoeswhere sales between 100000 and 200000;quit ;2.6.7 like ：判斷是否能匹配某些字符例如,選擇以 region 以 A 開頭的所有地區(qū)proc sql;select *from sashelp.shoeswhere Region like A%;quit ;這里注意有兩類通配符,%可以通配任意個任意字符,_只能通配一個任意字符2.6.8 =* ：類似匹配這里由于sashelp.shoes里沒有符合要求的數(shù)據(jù)，所有就用書上的例子

12、說明一下吧:Where lastname=* smith來的結(jié)果可能是：smith， smythe 等2.7 表達式這時用關(guān)鍵詞as來給新的列賦列名， 例如:我們可以通過已有的列進行計算來得到新的列,proc sql;select Region, Product,Sales,Stores,Sales/Stores as salesperstores from sashelp.shoesquit;這時結(jié)果就會多一列 salesperstores用來得到該地區(qū)該產(chǎn)品每個商店的平均銷售量。這里要注意的是，在創(chuàng)建表達式時，我們還可以在 SQL里用到SAS中的除LAG和DIFF之外的所 有函數(shù)。這里我們

13、還可以用表達式計算出來的結(jié)果來進行子集查詢，但一定要記住用calculated 關(guān)鍵詞。例如我們要找出商店平均銷售量大于 5000 的數(shù)據(jù)：方法一：proc sql;select Region, Product,Sales,Stores,Sales/Stores as salesperstoresfrom sashelp.shoeswhere Sales/Stores5000;quit ;方法二：proc sql;select Region, Product,Sales,Stores,Sales/Stores as salesperstoresfrom sashelp.shoeswhere c

14、alculated salesperstores5000;quit ;2.8 查詢結(jié)果展示2.8.1 order by 數(shù)據(jù)排序默認的排序方式是升序，我們可以用DESC關(guān)鍵詞來進行降序排列。例如以 sales降序排列數(shù)據(jù)：proc sql;select *from sashelp.shoesorder by Sales DESC;quit ;這里提示一下，我們可以用任意多列進行排序，包括表達式結(jié)果（不用calculated），但最好是選擇的列。2.8.2 LABEL 與 FORMATLABEL ：改變輸出變量名的內(nèi)容FORMAT ：改變列的值的輸出方式例如，改變 salesperstores

15、的 label 禾口 format proc sql;select Region, Product,Sales,Stores, Sales/Stores as salesperstores label=sales per stores format=dollar12.2 from sashelp.shoes;quit ;2.9 處理 SQL 常用函數(shù)MEAN 或 AVG ：均值COUNT或N或FREQ:非缺失值個數(shù)MAX ：最大值MIN :最小值NMISS :缺失值個數(shù)STD:標準差SUM :求和VAR :方差2.9.1 求和 sum proc sql;select Region, Produ

16、ct,Sales,Stores, sum(Sales,Inventory,Returns) as total from sashelp.shoes;quit ;2.9.2 求均值 avgproc sql;select Region, Product,Sales,Stores, avg(Sales) as salesavg from sashelp.shoes;quit ;2.9.3 分組求均值 group by proc sql;select Region,avg(Sales) as salesavgfrom sashelp.shoesgroup by Region;quit ;2.9.4 計

17、數(shù) countproc sql;select Region,count(*) as countfrom sashelp.shoesgroup by Region;quit ;2.9.5 HAVING 數(shù)據(jù)子集proc sql;select Region,count(*) as countfrom sashelp.shoesgroup by Regionhaving count(*) 50;quit ;其它的就不多作介紹了，多用用就熟悉了2.10 子查詢2.10.1找出regions平均sales大于全部平均 sales的region proc sql;select Region,avg(Sal

18、es) as salesavgfrom sashelp.shoesgroup by Regionhaving avg(Sales)(select avg(Sales) from sashelp.shoes);quit ;2.10.2 ANY 關(guān)鍵詞介紹ANY(20,30,40) 最終效果： 20ANY(20,30,40) 最終效果： 40=ANY(20,30,40) 最終效果： =20 or =30 or =40例如，選擇出 region為united state的sales小于任意region為africa的sales的數(shù)據(jù) proc sql;select Region,Salesfrom

19、sashelp.shoeswhere Region=United Statesand SalesALL (20,30,40) 最終效果： 40ALL (20,30,40) 最終效果： 20例如，選擇出 region為united state的sales小于所有region為africa的sales的數(shù)據(jù) proc sql;select Region,Salesfrom sashelp.shoeswhere Region=United Statesand Sales1 的話，將會出現(xiàn)錯誤。5.2 回滾 ROLLBACKS當我們用 INSERT 或 UPDATE 的時候，操作要等到發(fā)生錯誤的時候才

20、會停止，這時會出現(xiàn) 一個問題， 就是數(shù)據(jù)表的一些數(shù)據(jù)更新了， 而另一些數(shù)據(jù)沒更新， 這時如果我們要回到原來 的狀態(tài)，就需要用到 UNDO_POLICY 選項進行回滾。UNDO_POLICY 有三個選項：REQUIRED ：缺省選項，取消所有的更新或新插入的數(shù)據(jù)。這里要注意的是，此操作不一 定完全能成功。NONE ：阻止所有的與約束不符的更新或新數(shù)據(jù)OPTOINAL ：取消所有可以成功取消的更新或新插入的數(shù)據(jù) 本功能用得很少，所以只是翻譯一下，請大家查閱相關(guān)文獻。6 創(chuàng)建視圖與索引6.1 視圖視圖最大的好處是它只是一個存儲的查詢， 因此不包含任何數(shù)據(jù)， 這可以減少磁盤的使用空 間。其它的功能與數(shù)

21、據(jù)庫的表類似。創(chuàng)建視圖語法：Create View View-name as Query-expression ;例：proc sql noprint outobs=0;create View delayview asselect *from Delay;quit ;6.2 索引語法：Create Index index-nameOn table-name (column-name,column-name);視圖和索引都用得很少，所以這里就不作講解了，大家查閱相關(guān)文獻吧。7 維護表 這里主要講一下如何更新或刪除已存在的表的數(shù)據(jù)， 如何對一個表增加、 減少、 或改變其列 的屬性，如何刪除表、視圖

22、和索引。7.1 更新數(shù)據(jù)這里主要用 update 來更新表的數(shù)據(jù)，語法如下Update table-nameSet column-name=expression,Set colu mn- n ame=expressi on,Where expression;注意，這里的 where 一定要寫清楚，如果沒有的話，則會更新所有的數(shù)據(jù)。 例：proc sql noprint; update delayset delaycat=Delaywhere delay= 0;quit ;7.2 條件處理用 CASE 語句來實現(xiàn)條件處理。例如，對 SASHELP.shoes 根據(jù) sales 的大小進行分類：

23、語法：Select colu mn Case When when-condition then result-expressionEnd;例：proc sql;create table shoesrank asselect *,(casewhen sales100000 then goodelse badend) as rankfrom sashelp.shoes;quit ;7.3 刪除行語法：Delete from table-nameWhere expression;例：proc sql;Delete from shoesrankWhere Region=Africa;quit ;7.4

24、 改變列語法：Alter Table table-nameAdd column-definition,column- definition,Drop column-name,column- name,Modify column-definition,column- definition, 對已存在的數(shù)據(jù)表增加一個列：例：proc sql;alter table shoesrankadd addcolumn num format=comma10.2, addcolumnmore cha r( 10 );quit ;從已存在的數(shù)據(jù)表中刪除列，例：proc sql;alter table shoes

25、rankdrop addcolumn;quit ;修改某列的屬性，例：proc sql;alter table shoesrankmodify addcolumnmore char( 20) label=modify add label; quit ;7.5 刪除表、視圖、索引語法：Drop Table table-name,table-name, ;Drop View View-name,View- name, ;Drop Index index-name,index- name, ;From table-name;例：proc sql;drop table shoesrank;quit ;

26、SAS中的SQL語句完全教程之三：SQL過程步的其它特征SAS 中的 SQL 語句完全教程之三： SQL 過程步的其它特征本系列全部內(nèi)容主要以 SQL Processing with the SAS System (Course Notes) 為主進行講解， 本書是在網(wǎng)上下載下來的， 但忘了是在哪個網(wǎng)上下的， 故不能提供下載鏈接了， 需要的話可 以發(fā)郵件向我索取， 我定期郵給大家， 最后聲明一下所有資料僅用于學習， 不得用于商業(yè)目 的，否則后果自負。前面兩部分內(nèi)容都比較簡單， 本節(jié)內(nèi)容才是本系列要介紹的重點。 不過這里裝的內(nèi)容都是點 到即止，如果以后有時間，會進行更詳細地講解。1 SQL 過程

27、步選項SQL 過程步選項的作用主要是可以從更細節(jié)的方式去控制 SQL 過程步，并且可以在不執(zhí)行 過程的情況下對程序進行測試等。下面介紹一下列出來的選項，這些選項大多經(jīng)常用到，更多的選項可以參考 SAS 幫助。 INOBS ：進行一個查詢時，對每個源數(shù)據(jù)表進行 N 行限制，僅對這 N 行的數(shù)據(jù)進行查詢。 OUTOBS ：指定查詢輸出結(jié)果的觀測數(shù)LOOPS :指定SQL過程步內(nèi)循環(huán)的次數(shù)(此選項我用得比較少，誰明白的可以講一下，多 謝)NOPROMPT 和 PROMPT ：修改上述三個選項的效果， 從而讓你選擇是否繼續(xù)或停止選項的 效果。PRINT 和 NOPRINT ：控制是否打印選擇的數(shù)據(jù)結(jié)果

28、NONUMBER 和 NUMBER ：控制是否在第一列打印觀測值編號DOUBLE 和 NOBOUBLE ：輸入報表是否隔行顯示NOFLOW 和FLOW 和FLOW= n和FLOW= n m :指定列寬，n指定列寬，m指定行寬？ ？1.1 double 選項proc sql double;select flight,datefrom MarchUNIONselect flight,datefrom Delay;quit ;1.2 inobs 選項注意：這里 inobs 選項只讀取每個源表前 10 條數(shù)據(jù)進行后續(xù)的操作，如下面的日志所示。 兩個表都只讀取 10條數(shù)據(jù)進行關(guān)聯(lián)，最后得到 7 條關(guān)聯(lián)好

29、的數(shù)據(jù)。237 proc sql inobs=10;238 create table tmp as239 select a.*,b.*240 from March a,Delay b241 where a.flight=b.flight and a.date=b.date;WARNING:變量“ flight 已經(jīng)”存在于文件WORK.TMP中WARNING:變量date已經(jīng)存在于文件WORK.TMP中。WARNING:變量orig已經(jīng)存在于文件WORK.TMP中。WARNING:變量des已經(jīng)存在于文件WORK.TMP中。WARNING: Only 10 records were read

30、from WORK.DELAY （別名=B）由于 INOBS= 選項。WARNING: Only 10 records were read from WORK.MARCH （別名=A ） 由于 INOBS= 選 項。NOTE: 表 WORK.TMP 創(chuàng)建完成，有 7 行， 11 列。1.3 outobs 選項 這個選項的功能與 inobs 相似，不同的是，這個選項指定結(jié)果的觀測值數(shù)量，而不是讀取源 表的數(shù)量，這在我們控制輸出結(jié)果的數(shù)據(jù)量時非常有用。243 proc sql outobs=10;244 create table tmp1 as245 select a.*,b.*246 from

31、March a,Delay b247 where a.flight=b.flight and a.date=b.date;WARNING:變量“ flight已經(jīng)存在于文件WORK.TMP1中WARNING:變量date已經(jīng)存在于文件WORK.TMP1中。WARNING:變量orig已經(jīng)存在于文件WORK.TMP1中。WARNING:變量des已經(jīng)存在于文件WORK.TMP1中。WARNING: 語句由于 OUTOBS=10 選項而過早終止。 NOTE: 表 WORK.TMP1 創(chuàng)建完成，有 10 行，11 列。1.4 prompt 選項255這里 prompt 選項，可以讓你選擇是否還是按照原來的設(shè)置進行動作，或者繼續(xù)操作。如下 面的日志所以， 如果直接選擇停止， 則得到 1.2 的結(jié)果， 如果一直按繼續(xù)， 則得到如下結(jié)果：proc sql inobs=10 prompt;256create table tmp3 as257select a.*,b.*258from March a,Delay b259where a.flight=b.flight and a.date=b.date;變量“ flight 已經(jīng)”存在于文件WORK.TMP3中變量date已