SAS中用到的SQL语法及结构

SAS中的SQL语句完全教程之一：SQL简介与基本查询功能1 SQL过程步介绍1.1 SQL过程步可以实现下列功能：查询SAS数据集、从SAS数据集中生成报表、以不同方式实现数据集合并、创建或删除SAS数据集、视图、索引等、更新已存在的数据集、使得SAS系统可以使用SQL语句、可以和SAS的数据步进行替换使用。注意，SQL过程步并不是用来代替SAS数据步，也不是一个客户化的报表工具，而是数据处理用到的查询工具。1.2 SQL过程步的特征SQL过程步并不需要对每一个查询进行重复、每条语句都是单独处理、不需要print过程步就能打印出查询结果、也不用sort过程步进行排序、不需要run、要quit来结束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 ; 这里SELECT：指定被选择的列FROM：指定被查询的表名WHERE：子数据集的条件GROUP BY：将数据集通过group进行分类HAVING：根据GROUP BY的变量得到数据子集ORDER BY：对数据集进行排序2.2 SELECT语句的特征选择满足条件的数据、数据分组、对数据进行排序、对数据指定格式、一次最多查询32个表。这里还要提到的就是，在SAS系统中，对于表名和变量名一般不超过32个字符，对于库名，文件引用名，格式等不能超过8个字符2.3 Validate关键字Validate关键字只存在于select语句中、可以在不运行查询的情况下测试语句的语法、检查列名是否合法、对于不正确的查询将打印其消息。例：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;这里提示未执行，未提示错误，说明该语句没有语法错误。但是如果加入一个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选项来查看到底我们选择了哪些列：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选项来消除重复值。例如，我们要得到没有重复的所有地区的名称：proc sql; select distinct Region from sashelp.shoesquit;2.6 where子集查询2.6.1 比较运算符先列出where语句用到的比较运算符：LT 大于EQ = 等于LE = 大于或等于NE = 不等于例如，我们要查询sales大于100000的所有数据：proc sql; select * from sashelp.shoes where sales100000;quit;2.6.2 in：只要满足in里的任意一个值，表达式即为真，例如，我们要选择Region在Africa和Eastern Europe的所有数据：proc sql; select * from sashelp.shoes where Region in (Africa,Eastern Europe);quit;2.6.3 逻辑运算符OR | 或AND & 是NOT 非例如，选择Region在Africa和Eastern Europe，且销售额大于100000的所有数据：proc sql; select * from sashelp.shoes where Region in (Africa,Eastern Europe) and sales100000;quit;2.6.4 CONTAINS或?：判断某列是否包含指定字符串例如，选择列Region包含Afr的数据：proc sql; 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大于100000，但小于200000的所有数据：proc sql; select * from sashelp.shoes where sales between 100000 and 200000;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，出来的结果可能是：smith，smythe等2.7 表达式我们可以通过已有的列进行计算来得到新的列，这时用关键词as来给新的列赋列名，例如：proc sql; select Region, Product,Sales,Stores,Sales/Stores as salesperstores from sashelp.shoesquit;这时结果就会多一列salesperstores，用来得到该地区该产品每个商店的平均销售量。这里要注意的是，在创建表达式时，我们还可以在SQL里用到SAS中的除LAG和DIFF之外的所有函数。这里我们还可以用表达式计算出来的结果来进行子集查询，但一定要记住用calculated关键词。例如我们要找出商店平均销售量大于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;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 Region, 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 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 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 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 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 SalesALL (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 Sales1的话，将会出现错误。5.2 回滚ROLLBACKS当我们用INSERT或UPDATE的时候，操作要等到发生错误的时候才会停止，这时会出现一个问题，就是数据表的一些数据更新了，而另一些数据没更新，这时如果我们要回到原来的状态，就需要用到UNDO_POLICY选项进行回滚。UNDO_POLICY有三个选项：REQUIRED：缺省选项，取消所有的更新或新插入的数据。这里要注意的是，此操作不一定完全能成功。NONE：阻止所有的与约束不符的更新或新数据OPTOINAL：取消所有可以成功取消的更新或新插入的数据本功能用得很少，所以只是翻译一下，请大家查阅相关文献。6 创建视图与索引6.1 视图视图最大的好处是它只是一个存储的查询，因此不包含任何数据，这可以减少磁盘的使用空间。其它的功能与数据库的表类似。创建视图语法：Create View View-name asQuery-expression_r ;例：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_r,Set column-name=expression_r,Where expression_r;注意，这里的where一定要写清楚，如果没有的话，则会更新所有的数据。例：proc sql noprint;update delayset delaycat=Delaywhere delay=0;quit;7.2 条件处理用CASE语句来实现条件处理。例如，对SASHELP.shoes根据sales的大小进行分类：语法：Select columnCase When when-condition then result-expression_rEnd;例：proc sql; create table shoesrank as select *, (case when sales100000 then good else bad end) as rank from sashelp.shoes;quit;7.3 删除行语法：Delete from table-nameWhere expression_r;例：proc sql;Delete from shoesrankWhere 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 shoesrankadd addcolumn num format=comma10.2,addcolumnmore char(10);quit;从已存在的数据表中删除列，例：proc sql;alter table shoesrankdrop 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;SAS中的SQL语句完全教程之三：SQL过程步的其它特征1 SQL过程步选项SQL过程步选项的作用主要是可以从更细节的方式去控制SQL过程步，并且可以在不执行过程的情况下对程序进行测试等。下面介绍一下列出来的选项，这些选项大多经常用到，更多的选项可以参考SAS帮助。INOBS：进行一个查询时，对每个源数据表进行N行限制，仅对这N行的数据进行查询。OUTOBS：指定查询输出结果的观测数LOOPS：指定SQL过程步内循环的次数（此选项我用得比较少，谁明白的可以讲一下，多谢）NOPROMPT和PROMPT：修改上述三个选项的效果，从而让你选择是否继续或停止选项的效果。PRINT和NOPRINT：控制是否打印选择的数据结果NONUMBER和NUMBER：控制是否在第一列打印观测值编号DOUBLE和NOBOUBLE：输入报表是否隔行显示NOFLOW和FLOW和FLOW=n和FLOW=n m ：指定列宽，n指定列宽，m指定行宽？1.1 double选项proc sql double; select flight,date from March UNION select flight,date from Delay;quit;1.2 inobs选项注意：这里inobs选项只读取每个源表前10条数据进行后续的操作，如下面的日志所示。两个表都只读取10条数据进行关联，最后得到7条关联好的数据。237 proc