SAS中用到的SQL语法及结构.doc

《SAS中用到的SQL语法及结构.doc》由会员分享，可在线阅读，更多相关《SAS中用到的SQL语法及结构.doc（26页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SAS中的SQL语句完全教程之一：SQL简介与基本查询功能1 SQL过程步介绍1.1 SQL过程步可以实现下列功能：查询SAS数据集、从SAS数据集中生成报表、以不同方式实现数据集合并、创建或删除SAS数据集、视图、索引等、更新已存在的数据集、使得SAS系统可以使用SQL语句、可以和SAS的数据步进行替换使用。注意，SQL过程步并不是用来代替SAS数据步，也不是一个客户化的报表工具，而是数据处理用到的查询工具。1.2 SQL过程步的特征SQL过程步并不需要对每一个查询进行重复、每条语句都是单独处理、不需要print过程步就能打印出查询结果、也不用sort过程步进行排序、不需要run、要quit

2、来结束SQL过程步1.3 SQL过程步语句SELECT：查询数据表中的数据ALTER：增加、删除或修改数据表的列CREATE：创建一个数据表DELETE：删除数据表中的列DESCRIBE：列出数据表的属性DROP：删除数据表、视图或索引INSERT：对数据表插入数据RESET：没用过，不知道什么意思SELECT：选择列进行打印UPDATE：对已存在的数据集的列的值进行修改2 SQL基本查询功能2.1 SELECT语句基本语法介绍SELECT object-item FROM from-list GROUP BY group-by-item ORDER BY order-by-item ; 这里

3、SELECT：指定被选择的列FROM：指定被查询的表名WHERE：子数据集的条件GROUP BY：将数据集通过group进行分类HAVING：根据GROUP BY的变量得到数据子集ORDER BY：对数据集进行排序2.2 SELECT语句的特征选择满足条件的数据、数据分组、对数据进行排序、对数据指定格式、一次最多查询32个表。这里还要提到的就是，在SAS系统中，对于表名和变量名一般不超过32个字符，对于库名，文件引用名，格式等不能超过8个字符2.3 Validate关键字Validate关键字只存在于select语句中、可以在不运行查询的情况下测试语句的语法、检查列名是否合法、对于不正确的查询

4、将打印其消息。例：1 proc sql;2 validate3 select Region, Product,Sales4 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 选项，未执行语句。11 quit;这里提示未执行，未

5、提示错误，说明该语句没有语法错误。但是如果加入一个sashelp.shoes表里没有字段，这里就会出现错误，例：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选项来查看到底我们选

6、择了哪些列：17 proc sql feedback;18 select *19 from sashelp.shoes;NOTE: Statement transforms to: select SHOES.Region, SHOES.Product, SHOES.Subsidiary, SHOES.Stores, SHOES.Sales, SHOES.Inventory, SHOES.Returns from SASHELP.SHOES;20 quit;这时，我们可以看到从sashelp.shoes表中选择了8个列2.5 消除重复值我们可以用distinct选项来消除重复值。例如，我们要得到

7、没有重复的所有地区的名称：proc sql; select distinct Region from sashelp.shoesquit;2.6 where子集查询2.6.1 比较运算符先列出where语句用到的比较运算符：LT 大于EQ = 等于LE = 大于或等于NE = 不等于例如，我们要查询sales大于的所有数据：proc sql; select * from sashelp.shoes where sales;quit;2.6.2 in：只要满足in里的任意一个值，表达式即为真，例如，我们要选择Region在Africa和Eastern Europe的所有数据：proc sql;

8、select * from sashelp.shoes where Region in (Africa,Eastern Europe);quit;2.6.3 逻辑运算符OR | 或AND & 是NOT 非例如，选择Region在Africa和Eastern Europe，且销售额大于的所有数据：proc sql; select * from sashelp.shoes where Region in (Africa,Eastern Europe) and sales;quit;2.6.4 CONTAINS或?：判断某列是否包含指定字符串例如，选择列Region包含Afr的数据：proc sql

9、; select * from sashelp.shoes where Region ? Afr;quit;2.6.5 IS NULL或IS MISSING：判断某列数据是否为空例如，如果找出Region为空的数据：proc sql; select * from sashelp.shoes where Region is missing;quit;注意，这里我们还可以用以下表达式对where语句进行替换。如果region为数值型变量，则可以用region=.，如果region为字符型变量，则可以用region= 进行替换。2.6.6 Between and：选择某一区间的数据例如选择sales

10、大于，但小于的所有数据：proc sql; select * from sashelp.shoes where sales between and ;quit;2.6.7 like：判断是否能匹配某些字符例如，选择以region以A开头的所有地区proc sql; select * from sashelp.shoes where Region like A%;quit;这里注意有两类通配符，%可以通配任意个任意字符，_只能通配一个任意字符2.6.8 =*：类似匹配这里由于sashelp.shoes里没有符合要求的数据，所有就用书上的例子说明一下吧：Where lastname=* smith

11、，出来的结果可能是：smith，smythe等2.7 表达式我们可以通过已有的列进行计算来得到新的列，这时用关键词as来给新的列赋列名，例如：proc sql; select Region, Product,Sales,Stores,Sales/Stores as salesperstores from sashelp.shoesquit;这时结果就会多一列salesperstores，用来得到该地区该产品每个商店的平均销售量。这里要注意的是，在创建表达式时，我们还可以在SQL里用到SAS中的除LAG和DIFF之外的所有函数。这里我们还可以用表达式计算出来的结果来进行子集查询，但一定要记住用c

12、alculated关键词。例如我们要找出商店平均销售量大于5000的数据：方法一：proc sql; select Region, Product,Sales,Stores,Sales/Stores as salesperstores from sashelp.shoes where Sales/Stores5000;quit;方法二：proc sql; select Region, Product,Sales,Stores,Sales/Stores as salesperstores from sashelp.shoes where calculated salesperstores5000

13、;quit;2.8 查询结果展示2.8.1 order by数据排序默认的排序方式是升序，我们可以用DESC关键词来进行降序排列。例如以sales降序排列数据：proc sql; select * from sashelp.shoes order by Sales DESC;quit;这里提示一下，我们可以用任意多列进行排序，包括表达式结果（不用calculated），但最好是选择的列。2.8.2 LABEL与FORMATLABEL：改变输出变量名的内容FORMAT：改变列的值的输出方式例如，改变salesperstores的label和formatproc sql; select Regio

14、n, Product,Sales,Stores, Sales/Stores as salesperstores label=sales per stores format=dollar12.2 from sashelp.shoes;quit;2.9 处理SQL常用函数MEAN或AVG：均值COUNT或N或FREQ：非缺失值个数MAX：最大值MIN：最小值NMISS：缺失值个数STD：标准差SUM：求和VAR：方差2.9.1 求和sumproc sql; select Region, Product,Sales,Stores, sum(Sales,Inventory,Returns) as to

15、tal 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 byproc sql; select Region, avg(Sales) as salesavg from sashelp.shoes group by Region;quit;2.9.4 计数countproc sql; select Region,count(*) as count from

16、 sashelp.shoes group by Region;quit;2.9.5 HAVING数据子集proc sql; select Region,count(*) as count from sashelp.shoes group by Region having count(*)50;quit;其它的就不多作介绍了，多用用就熟悉了2.10子查询2.10.1 找出regions平均sales大于全部平均sales的regionproc sql; select Region, avg(Sales) as salesavg from sashelp.shoes group by Region

17、 having avg(Sales) (select avg(Sales) from sashelp.shoes);quit;2.10.2 ANY关键词介绍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的数据proc sql; select Region,Sales from sashelp.shoes where Region=United States and SalesA

18、LL (20,30,40) 最终效果：40ALL (20,30,40) 最终效果：20例如，选择出region为united state的sales小于所有region为africa的sales的数据proc sql; select Region,Sales from sashelp.shoes where Region=United States and Salesall (select Sales from sashelp.shoes where Region=Africa);quit;2.10.4 EXISTS与NOT EXISTSproc sql; select * from sash

19、elp.shoes where exists (select * from sashelp.orsales);quit;SAS中的SQL语句完全教程之二：数据合并与建表、建视图索引等1 连接joins分为内连接inner joins和外连接outer joins内连接：仅返回匹配的数据，最多可以有32个表同时进行内连接外连接：返回所有匹配的数据和非匹配的数据，一次只能有两个表或视图进行外连接迪卡尔积：返回表内所有可能的匹配情况。例如表A有10*20的数据，表B有30*40的数据，则两个表的迪卡尔积有（10+30）*（20+40）=40*60的数据我们先建立两个数据集：data march; i

20、nput 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 01M

21、AR94 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 03MAR9

22、4 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 05MAR9

23、4 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

24、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 o

25、rig $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 L

26、GA 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

27、 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 Internatio

28、nal 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 I

29、nternational 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

30、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

31、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过程步会对这个迪卡尔积的数据集进行优化，将其划分成小块数据进行

32、处理。2 复杂的连接这里因为找不到很好的例子来说明如何处理复杂的连接，因此就不讲解了，大概说一下思路吧：简单地说，对于复杂的查询，我们应该将该查询分解成几个小的子查询，然后对每个子查询的结果进行测试，最后将所有的子查询结合起来就组成了这个复杂的查询。这样会比一来就写复杂的查询容易得多。3 数据集SET操作这里有四类SET操作，分别是EXCEPT、INTERSECT、UNION、OUTER UNION。EXCEPT：得到除第二个数据集以外的所有第一个数据集里的数据INTERSECT：得到第一个数据集和第二个数据集都有的数据UNION：得到两个数据集所有的数据，这里如果两个数据集有相同的数据，重复

33、数据只出现一次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;注意：这里重复值

34、已经被删除。如果要得到所有的包含重复值的数据，就要用到关键词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 I

35、NTERSECT 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 se

36、lect 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, endda

37、te 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=07MA