SAS中的SQL语句完全教程之二：数据合并与建表、建视图索引等
本系列全部内容主要以《SQL Processing with the SAS System (Course
Notes)》为主进行讲解，本书是在网上下载下来的，但忘了是在哪个网上下的，故不能提供下载链接了，需要的话可以发邮件向我索取，我定期邮给大家，最后声明一下所有资料仅用于学习，不得用于商业目的，否则后果自负。
1 连接joins分为内连接inner joins和外连接outer joins
内连接：仅返回匹配的数据，最多可以有32个表同时进行内连接
外连接：返回所有匹配的数据和非匹配的数据，一次只能有两个表或视图进行外连接
迪卡尔积：返回表内所有可能的匹配情况。例如表A有10*20的数据，表B有30*40的数据，则两个表的迪卡尔积有(10+30)*(20+40)=40*60的数据
我们先建立两个数据集：
data march;
input flight
$3. +5 date date7. +3 depart time5. +2 orig
$3.
+3 dest $3. +7 miles +6
boarded +6 capacity;
format date
date7. depart time5.;
informat date
date7. depart time5.;
cards;
219 01MAR94 9:31 LGA LON 3442 198 250
622 01MAR94 12:19 LGA FRA 3857 207 250
132 01MAR94 15:35 LGA YYZ 366 115 178
271 01MAR94 13:17 LGA PAR 3635 138 250
302 01MAR94 20:22 LGA WAS 229 105 180
114 02MAR94 7:10 LGA LAX 2475 119 210
202 02MAR94 10:43 LGA ORD 740 120 210
219 02MAR94 9:31 LGA LON 3442 147 250
132 02MAR94 15:35 LGA YYZ 366 106 178
202 03MAR94 10:43 LGA ORD 740 118 210
219 03MAR94 9:31 LGA LON 3442 197 250
622 03MAR94 12:19 LGA FRA 3857 180 250
271 03MAR94 13:17 LGA PAR 3635 147 250
202 04MAR94 10:43 LGA ORD 740 148 210
219 04MAR94 9:31 LGA LON 3442 232 250
622 04MAR94 12:19 LGA FRA 3857 137 250
132 04MAR94 15:35 LGA YYZ 366 117 178
271 04MAR94 13:17 LGA PAR 3635 146 250
302 04MAR94 20:22 LGA WAS 229 115 180
114 05MAR94 7:10 LGA LAX 2475 117 210
202 05MAR94 10:43 LGA ORD 740 104 210
219 05MAR94 9:31 LGA LON 3442 160 250
622 05MAR94 12:19 LGA FRA 3857 185 250
132 05MAR94 15:35 LGA YYZ 366 157 178
271 05MAR94 13:17 LGA PAR 3635 177 250
114 06MAR94 7:10 LGA LAX 2475 128 210
202 06MAR94 10:43 LGA ORD 740 115 210
219 06MAR94 9:31 LGA LON 3442 163 250
132 06MAR94 15:35 LGA YYZ 366 150 178
302 06MAR94 20:22 LGA WAS 229 66 180
114 07MAR94 7:10 LGA LAX 2475 160 210
132 07MAR94 15:35 LGA YYZ 366 164 178
271 07MAR94 13:17 LGA PAR 3635 155 250
302 07MAR94 20:22 LGA WAS 229 135 180
;
run;
data delay;
input flight
$3. +5 date date7. +2 orig $3. +3 dest $3.
+3
delaycat $15. +2 destype $15. +8 delay;
informat date
date7.;
format date
date7.;
cards;
114 01MAR94 LGA LAX 1-10
Minutes Domestic 8
202 01MAR94 LGA ORD No
Delay Domestic -5
622 01MAR94 LGA FRA No
Delay International -5
132 01MAR94 LGA YYZ 11+
Minutes International 14
302 01MAR94 LGA WAS No
Delay Domestic -2
114 02MAR94 LGA LAX No
Delay Domestic 0
202 02MAR94 LGA ORD 1-10
Minutes Domestic 5
219 02MAR94 LGA LON 11+
Minutes International 18
622 02MAR94 LGA FRA No
Delay International 0
132 02MAR94 LGA YYZ 1-10
Minutes International 5
271 02MAR94 LGA PAR 1-10
Minutes International 4
302 02MAR94 LGA WAS No
Delay Domestic 0
114 03MAR94 LGA LAX No
Delay Domestic -1
202 03MAR94 LGA ORD No
Delay Domestic -1
219 03MAR94 LGA LON 1-10 Minutes
International 4
622 03MAR94 LGA FRA No
Delay International -2
132 03MAR94 LGA YYZ 1-10
Minutes International 6
271 03MAR94 LGA PAR 1-10
Minutes International 2
302 03MAR94 LGA WAS 1-10
Minutes Domestic 5
114 05MAR94 LGA LAX No
Delay Domestic -2
202 06MAR94 LGA ORD No
Delay Domestic -3
219 06MAR94 LGA LON 11+
Minutes International 27
132 06MAR94 LGA YYZ 1-10
Minutes International 7
302 06MAR94 LGA WAS 1-10
Minutes Domestic 1
622 07MAR94 LGA FRA 11+
Minutes International 21
132 07MAR94 LGA YYZ No
Delay International -2
271 07MAR94 LGA PAR 1-10
Minutes International 4
302 07MAR94 LGA WAS No
Delay Domestic 0
;
run;
1.1 内连接
proc sql;
create table
innerjoins as
select
a.*,b.*
from March
a,Delay b
where
a.flight=b.flight and a.date=b.date;
quit;
1.2 外连接
1.2.1 左连接left join
proc sql;
create table
leftjoins as
select *
from March
a
left join
Delay b
on
a.flight=b.flight and a.date=b.date;
quit;
1.2.2 右连接right join
proc sql;
create table
rightjoins as
select *
from March
a
right join
Delay b
on
a.flight=b.flight and a.date=b.date;
quit;
1.2.3 全连接full join
proc sql;
create table
fulljoins as
select *
from March
a
full join
Delay b
on
a.flight=b.flight and a.date=b.date;
quit;
1.3 迪卡尔积
proc sql;
create table
cartesian as
select
a.*,b.*
from March
a,Delay b;
quit;
这里再大概说明一下内外连接的实现的基本原理：首先生成两个数据表的迪卡尔积，然后再根据where语句来选择符合条件的数据作为输出结果。当然，在实际处理过程中，SQL过程步会对这个迪卡尔积的数据集进行优化，将其划分成小块数据进行处理。
2 复杂的连接
这里因为找不到很好的例子来说明如何处理复杂的连接，因此就不讲解了，大概说一下思路吧：简单地说，对于复杂的查询，我们应该将该查询分解成几个小的子查询，然后对每个子查询的结果进行测试，最后将所有的子查询结合起来就组成了这个复杂的查询。这样会比一来就写复杂的查询容易得多。
3 数据集SET操作
这里有四类SET操作，分别是EXCEPT、INTERSECT、UNION、OUTER UNION。
EXCEPT：得到除第二个数据集以外的所有第一个数据集里的数据
INTERSECT：得到第一个数据集和第二个数据集都有的数据
UNION：得到两个数据集所有的数据，这里如果两个数据集有相同的数据，重复数据只出现一次
OUTER UNION：得到两个数据集所有的数据。
还有两个关键词来修改SET操作的行为：ALL和CORRESPONDING
ALL：并不删除重复值，不能与OUTER
UNION合用(注意，用ALL关键词，一种情况是你不管是否会有重复值，另一个情况是不可能出现重复值，例如有主键的数据)
CORRESPONDING：只保留两个数据集都有的字段，一般简写为CORR
3.1 EXCEPT
找出所有没有延迟的航班和日期：
proc sql;
select
flight,date
from March
except
select
flight,date
from Delay;
quit;
注意：这里重复值已经被删除。如果要得到所有的包含重复值的数据，就要用到关键词ALL：
proc sql;
select
flight,date
from March
except
ALL
select
flight,date
from Delay;
quit;
如果只保留两个数据集都有的字段，则用到关键词CORRESPONDING(可简写为CORR)：
proc sql;
select *
from March
except
CORR
select *
from Delay;
quit;
3.2 INTERSECT
找出所有延迟的航班和日期：
proc sql;
select
flight,date
from March
INTERSECT
select
flight,date
from Delay;
quit;
关键词ALL和CORRESPONDING与在EXCEPT中一样，这里不再作介绍
3.3 UNION
选择所有的数据，这里的重复值只出现一次
proc sql;
select
flight,date
from March
UNION
select
flight,date
from Delay;
quit;
关键词ALL和CORRESPONDING与在EXCEPT中一样，这里不再作介绍
3.4 OUTER UNION
proc sql;
select
flight,date
from March
OUTER
UNION
select
flight,date
from Delay;
quit;
这里，如果我们用到关键词CORR，就可以将相同字段的数据结合到一起：
proc sql;
select *
from March
OUTER
UNION CORR
select *
from Delay;
quit;
4 建表
4.1 建空表
4.1.1 通过指定变量建空表
proc sql noprint;
create table
percent
(varname
char(30),
Industry char(4),
begindate num format date9. label='the beginning
date',
enddate num format date9. label='the ending date',
P_1 num,
P_5 num,
P_95 num,
P_99 num);
quit;
4.1.2 拷贝数据集来建空表
proc sql noprint;
create table
Delaycopy(keep=flight date)
like
Delay;
quit;
4.1.3 查询语句中，用outobs选项来建空表
proc sql noprint outobs=0;
create table
flight114 as
select *
from
Delay;
quit;
4.2 对数据表增加数据
主要有三种方法：
4.2.1 SET语句
proc sql noprint;
insert into flight114
set flight='302' ,
date='07MAR94'd,
orig='LGA',
dest='WAS',
delaycat='No
Delay',
destype='Domestic',
delay=0;
quit;
4.2.2 VALUES语句
proc sql noprint;
insert into flight114
values('271','07MAR94'd,'LGA','PAR','1-10
Minutes','International',4);
quit;
4.2.3 条件查询 ：建表和导入数据同时完成
proc sql noprint;
create table
flight114 as
select *
from Delay
where
flight='114';
quit;
5 完整性约束
完整性约束的好处是保证SAS数据集的连续性和正确性，它在我们更新数据或插入新的数据时，验证新的数据是否符合该变量的约束条件。
完整性约束需要8.0以上版本，符合ANSI标准，可以在表建立时或表有数据后建立约束，但不能被用于视图，也不能用于低于8.0版本的SAS系统建立的数据集。
五大类完整性限制：NOT NULL、CHECK、UNIQUE、PRIMARY KEY、FOREIGN KEY。
NOT NULL：不许出现缺失值
CHECK：指定该变量可以输入哪些值
UNIQUE：每个值必须是唯一的，其值可以为空，但只能有一个值为空
PRIMARY KEY：主键，每个值必须是唯一且非空的
FOREIGN KEY：其它表的主键，即外键，其值为关联表的主键的值且非空
5.1 check
例：
proc sql noprint;
create table
percent
(varname char(30),
Industry char(4),
begindate num format date9. label='the beginning
date',
enddate num format date9. label='the ending date',
percent num,
CONSTRAINT percent _check check
(percent le 1.0));
quit;
这时，如果我们插入的数据中，percent>1的话，将会出现错误。
5.2 回滚ROLLBACKS
当我们用INSERT或UPDATE的时候，操作要等到发生错误的时候才会停止，这时会出现一个问题，就是数据表的一些数据更新了，而另一些数据没更新，这时如果我们要回到原来的状态，就需要用到UNDO_POLICY选项进行回滚。
UNDO_POLICY有三个选项：
REQUIRED：缺省选项，取消所有的更新或新插入的数据。这里要注意的是，此操作不一定完全能成功。
NONE：阻止所有的与约束不符的更新或新数据
OPTOINAL：取消所有可以成功取消的更新或新插入的数据
本功能用得很少，所以只是翻译一下，请大家查阅相关文献。
6 创建视图与索引
6.1 视图
视图最大的好处是它只是一个存储的查询，因此不包含任何数据，这可以减少磁盘的使用空间。其它的功能与数据库的表类似。
创建视图语法：
Create View View-name as
Query-expression ;
例：
proc sql noprint outobs=0;
create View
delayview as
select *
from
Delay;
quit;
6.2 索引
语法：
Create Index
index-name
On table-name (column-name,column-name);
视图和索引都用得很少，所以这里就不作讲解了，大家查阅相关文献吧。
7 维护表
这里主要讲一下如何更新或删除已存在的表的数据，如何对一个表增加、减少、或改变其列的属性，如何删除表、视图和索引。
7.1 更新数据
这里主要用update来更新表的数据，语法如下
Update table-name
Set column-name=expression,
Set column-name=expression,…
Where expression;
注意，这里的where一定要写清楚，如果没有的话，则会更新所有的数据。
例：
proc sql noprint;
update delay
set delaycat='Delay'
where delay=0;
quit;
7.2 条件处理
用CASE语句来实现条件处理。例如，对SASHELP.shoes根据sales的大小进行分类：
语法：
Select column…
Case
When when-condition then result-expression
< When when-condition then
result-expression>
End;
例：
proc sql;
create table
shoesrank as
select
*,
(case
when sales>100000 then 'good'
else 'bad'
end) as rank
from
sashelp.shoes;
quit;
7.3 删除行
语法：
Delete from table-name
Where expression;
例：
proc sql;
Delete from shoesrank
Where Region='Africa';
quit;
7.4 改变列
语法：
Alter Table table-name
Add column-definition,column-definition,…
Drop column-name,column-name,…
Modify column-definition,column-definition,…
对已存在的数据表增加一个列：例：
proc sql;
alter table shoesrank
add addcolumn num format=comma10.2,
addcolumnmore char(10);
quit;
从已存在的数据表中删除列，例：
proc sql;
alter table shoesrank
drop addcolumn;
quit;
修改某列的属性，例：
proc sql;
alter table shoesrank
modify 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;