《SAS编程与数据挖掘商业案例》学习笔记之十七
繼續讀書筆記，本次重點sas sql語句，因為sql內容多且復雜，本文僅僅介紹商業應用中經常使用的而且easy出錯的地方，內容包含：單表操作、多表關聯、子查詢以及merge和join的差別
1.單表操作
eg1：
Proc sql?outobs=10。?*可選項，功能類似于data步中的obs數據集選項
???create table class as
Select??name,
?????????????case when sex eq 'M' then "1" when sex eq 'F'??then "2"??else "3"
??????????????end as sex_tran??label="sextrans",???*輸出數據集中作為sex_trans的中文標簽
?????????????sum((weight-20)) as new1 format=16.2,??*sas查詢特有的形式
?????????????sum((height-10))??as new2??format=16.2,
?????????????(calculated??new1 - calculated??new2) as new,??*calculated是sas在sql中特有的語句，用于對產生的新變量再操作
From sashelp.class(where=(height between 50 and 70))
Group by name,calculated sex_tran;
eg2：
proc sql;
create table test1 as
select
id,
max (txn_seq) as txn_seq,
sum (amount) as sum_amt
from chap11.having1
group by id
having calculated sum_amt ge 70
;
quit;
注：having語句出如今group by后面，假設沒喲group by語句，系統默認having語句和where語句效果同樣
proc sql;
create table test2 as
select
id,
txn_seq,
amount
from chap11.having1
group by id
having txn_seq= max (txn_seq)
;
quit;
注：having語句和group by語句同一時候出現時，select后面不一定須要匯總函數如sum等，上例中。依照每個id取最大的txn_seq
proc sql;
create table test3 as
select
id,
txn_seq,
amount
from chap11.having2
group by id
having txn_seq = max (txn_seq)
;
quit;
having語句和max或min結合使用時，一定要注意having后面的變量在每個id中的唯一性。
2.多表關聯
左聯接在join變量是一對一的情況下，假設where在表的外面，則where條件是先關聯表。后篩選數據，假設where在表中，則是先篩選數據后關聯表，and也是先篩選數據后關聯表。因而表內寫where和表外寫and是全然一樣的。
下面程序。2和3全然一樣，可是1和后面兩個不一樣
eg：
proc sql;
????create table leftjoin1 as
????select
case
when b.rmb_id eq . then a.id
????else b.rmb_id
????end as all_rmb,
????a.id,
????b.rmb_id,
????b.usd_id
????from chap11.left_join_1 as a
????left join chap11.left_join_2 as b
????on a.id=b.usd_id
????where rmb_id le 4
;
quit;
proc sql;
create table leftjoin2 as
select
case
when b.rmb_id eq . then a.id
else b.rmb_id
end as all_rmb,
a.id,
b.rmb_id,
b.usd_id
from
chap11.left_join_1 as a
left join chap11.left_join_2
(where=(rmb_id le 4)) as b
on a.id=b.usd_id
;
quit;
proc sql;
create table leftjoin3 as
select
case
when b.rmb_id eq . then a.id
else b.rmb_id
end as all_rmb,
a.id,
b.rmb_id,
b.usd_id
from chap11.left_join_1 as a
left join chap11.left_join_2 as b
on a.id=b.usd_id
and rmb_id le 4
;
quit;
3.子查詢
in子查詢效率比join低非常多，而exist子查詢效率更低；
4.merge和sql的比較
在“一對一”和“多對一”的情況下，效果全然同樣?？墒窃凇岸鄬Χ唷鼻闆r下。則全然不同。
創建測試數據集
| data a1; input x y @@; cards; 101 1??102 3 103 30 104 5 ; run; ? | data a2; input x y @@; cards; 101 10 102 30 103 5??105 50 ; run; | data a3; input x y z1 @@; cards; 101 11 1 102 33 2 102 300 3 104 5 4 ; run; | data a4; input x y z2 @@; cards; 101 1 5 102 30 6 102 5 7 102 100 8 102 200 9 105 50 10 ; run; |
eg1：求a1和a2的交集
| sql實現： | merge實現 |
| proc sql; select a1.x ,a2.y from a1 join a2 on a1.x=a2.x ; quit; | proc sort data=a1;by x;run; proc sort data=a2;by x;run; data a12; merge a1(in=ina) a2(in=inb); by x; if ina and inb; proc print;run; |
?注：sql是通過內連接實現，merge通過if ina and inb實現
?
eg2：用數據集a2來更新數據集a1
| sql實現： | merge實現 |
| proc sql; select a1.x ,case when a2.y is not null then a2.y else a1.y end as yy from a1 left join a2 on a1.x=a2.x ; quit; | proc sort data=a1;by x;run; proc sort data=a2;by x;run; ? data a12; merge a1(in=ina) a2(in=inb); by x; if ina; proc print;run; |
注：sql通過左聯接方式實現。merge通過if ina方式實現。等價于左聯接
?
eg3：用數據集a2來更新數據集a1，同一時候保留兩個數據集全部觀測
| sql實現： | merge實現 |
| proc sql; select coalesce(a1.x,a2.x) as x ,coalesce(a2.y,a1.y) as yy from a1 full join a2 on a1.x=a2.x ; quit; | proc sort data=a1;by x;run; proc sort data=a2;by x;run; ? data a12; merge a1 a2; by x; ??????run; proc print;run; |
注：sql通過全連接方式實現，需求中須要用a2全部變量更新a1，所以一定要把a2變量放在前面，被更新的數據集放在后面，可是對join的匹配變量，對這樣的順序無要求；merge沒有使用in=選項，等價于全連接；
eg4：多對多
| sql實現 | merge實現 |
| proc sql; select a3.x,a4.y ,a3.z1,a4.z2 from a3 join a4 on a3.x=a4.x ; quit; | proc sort data=a3;by x;run; proc sort data=a4;by x;run; ? data a34; merge a3(in=ina) a4(in=inb); by x; if ina and inb; run; proc print;run; |
注：sql形式會出現反復值，匹配到的數據會進行笛卡爾。
而merge則由于if ina and inb的作用，避免了反復
5.創建表
復制已有的表屬性
proc sql;
create table a
like sashelp.class;
describe table a;
quit;
6.行操作
加入行操作
| 使用set語句 | 使用value語句 | 使用select語句 |
| proc sql; insert into countries set name='bangladesh', capital='dhaka', population=126391060 set name='japan', capital='tokyo', population=126352003; quit; | proc sql; insert into countries values ('pakistan', 'islamabad', 123060000) values ('nigeria', 'lagos', 99062000); quit; | proc sql; create table newconntries like countries; insert into newconntries select * from countries where population ge 120000000; quit; |
?
最后注意：
多表關聯時。最好不要超過5個表。除非都是代碼表。否則sql會產生非常大的暫時空間。從而減少程序性能
除非必須，否則優先使用關聯，而不要用子查詢
在使用set操作符時。假設set表沒有反復行，則使用union all會提高性能
假設有索引。盡可能用索引和where語句結合
盡量避免多對多join
與50位技術專家面對面20年技術見證，附贈技術全景圖總結
以上是生活随笔為你收集整理的《SAS编程与数据挖掘商业案例》学习笔记之十七的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql 连接数的最大数
- 下一篇： ECMALL数据库关系模型的实现