SAS中的SQL语句完全教程之二.doc

SAS中的SQL语句完全教程之二：数据合并与建表、建视图索引等本系列全部内容主要以SQL Processing with the SAS System (Course Notes)为主进行讲解，本书是在网上下载下来的，但忘了是在哪个网上下的，故不能提供下载链接了，需要的话可以发邮件向我索取，我定期邮给大家，最后声明一下所有资料仅用于学习，不得用于商业目的，否则后果自负。转载请注明出处：/s/blog_5d3b177c0100cm1t.html1 连接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 250622 01MAR94 12:19 LGA FRA 3857 207 250132 01MAR94 15:35 LGA YYZ 366 115 178271 01MAR94 13:17 LGA PAR 3635 138 250302 01MAR94 20:22 LGA WAS 229 105 180114 02MAR94 7:10 LGA LAX 2475 119 210202 02MAR94 10:43 LGA ORD 740 120 210219 02MAR94 9:31 LGA LON 3442 147 250132 02MAR94 15:35 LGA YYZ 366 106 178202 03MAR94 10:43 LGA ORD 740 118 210219 03MAR94 9:31 LGA LON 3442 197 250622 03MAR94 12:19 LGA FRA 3857 180 250271 03MAR94 13:17 LGA PAR 3635 147 250202 04MAR94 10:43 LGA ORD 740 148 210219 04MAR94 9:31 LGA LON 3442 232 250622 04MAR94 12:19 LGA FRA 3857 137 250132 04MAR94 15:35 LGA YYZ 366 117 178271 04MAR94 13:17 LGA PAR 3635 146 250302 04MAR94 20:22 LGA WAS 229 115 180114 05MAR94 7:10 LGA LAX 2475 117 210202 05MAR94 10:43 LGA ORD 740 104 210219 05MAR94 9:31 LGA LON 3442 160 250622 05MAR94 12:19 LGA FRA 3857 185 250132 05MAR94 15:35 LGA YYZ 366 157 178271 05MAR94 13:17 LGA PAR 3635 177 250114 06MAR94 7:10 LGA LAX 2475 128 210202 06MAR94 10:43 LGA ORD 740 115 210219 06MAR94 9:31 LGA LON 3442 163 250132 06MAR94 15:35 LGA YYZ 366 150 178302 06MAR94 20:22 LGA WAS 229 66 180114 07MAR94 7:10 LGA LAX 2475 160 210132 07MAR94 15:35 LGA YYZ 366 164 178271 07MAR94 13:17 LGA PAR 3635 155 250302 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 8202 01MAR94 LGA ORD No Delay Domestic -5622 01MAR94 LGA FRA No Delay International -5132 01MAR94 LGA YYZ 11+ Minutes International 14302 01MAR94 LGA WAS No Delay Domestic -2114 02MAR94 LGA LAX No Delay Domestic 0202 02MAR94 LGA ORD 1-10 Minutes Domestic 5219 02MAR94 LGA LON 11+ Minutes International 18622 02MAR94 LGA FRA No Delay International 0132 02MAR94 LGA YYZ 1-10 Minutes International 5271 02MAR94 LGA PAR 1-10 Minutes International 4302 02MAR94 LGA WAS No Delay Domestic 0114 03MAR94 LGA LAX No Delay Domestic -1202 03MAR94 LGA ORD No Delay Domestic -1219 03MAR94 LGA LON 1-10 Minutes International 4622 03MAR94 LGA FRA No Delay International -2132 03MAR94 LGA YYZ 1-10 Minutes International 6271 03MAR94 LGA PAR 1-10 Minutes International 2302 03MAR94 LGA WAS 1-10 Minutes Domestic 5114 05MAR94 LGA LAX No Delay Domestic -2202 06MAR94 LGA ORD No Delay Domestic -3219 06MAR94 LGA LON 11+ Minutes International 27132 06MAR94 LGA YYZ 1-10 Minutes International 7302 06MAR94 LGA WAS 1-10 Minutes Domestic 1622 07MAR94 LGA FRA 11+ Minutes International 21132 07MAR94 LGA YYZ No Delay International -2271 07MAR94 LGA PAR 1-10 Minutes International 4302 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 joinproc 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 joinproc 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 joinproc 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和CORRESPONDINGALL：并不删除重复值，不能与OUTER UNION合用（注意，用ALL关键词，一种情况是你不管是否会有重复值，另一个情况是不可能出现重复值，例如有主键的数据）CORRESPONDING：只保留两个数据集都有的字段，一般简写为CORR3.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 UNIONproc 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 flight114set flight=302 , date=07MAR94d, orig=LGA, dest=WAS, delaycat=No Delay, destype=Domestic, delay=0;quit;4.2.2 VALUES语句proc sql noprint;insert into flight114values(271,07MAR94d,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;这时，如果我们插入的数据中，percent1的话，将会出现错误。5.2 回滚ROLLBACKS当我们用INSERT或UPDATE的时候，操作要等到发生错误的时候才会停止，这时会出现一个问题，就是数据表的一些数据更新了，而另一些数据没更新，这时如果我们要回到原来的状态，就需要用到UNDO_POLICY选项进行回滚。UNDO_POLICY有三个选项：REQUIRED：缺省选项，取消所有的更新或新插入的数据。这里要注意的是，此操作不一定完全能成功。NONE：阻止所有的与约束不符的更新或新数据OPTOINAL：取消所有可以成功取消的更新或新插入的数据本功能用得很少，所以只是翻译一下，请大家查阅相关文献。6 创建视图与索引6.1 视图视图最大的好处是它只是一个存储的查询，因此不包含任何数据，这可以减少磁盘的使用空间。其它的功能与数据库的表类似。创建视图语法：Create View View-name asQuery-expression;例：proc sql noprint outobs=0; create View delayview as select * from Delay;quit;6.2 索引语法：Create Index index-nameOn 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 delayset delaycat=Delaywhere delay=0;quit;7.2 条件处理用CASE语句来实现条件处理。例如，对SASHELP.shoes根据sales的大小进行分类：语法：Select columnCase Wh