SAS中的SQL语句完全教程

1、SAS 中的 SQL 语句完全教程之一： SQL 简介与基本查询功能 本系列全部内容主要以 SQL Processing with the SAS System (Course Notes) 为主进行讲解， 本书是在网上下载下来的， 但忘了是在哪个网上下的， 故不能提供下载链接了， 需要的话可 以发邮件向我索取， 我定期邮给大家， 最后声明一下所有资料仅用于学习， 不得用于商业目 的，否则后果自负。 1 SQL 过程步介绍 1.1 SQL 过程步可以实现下列功能： 查询 SAS 数据集、从 SAS 数据集中生成报表、 以不同方式实现数据集合并、 创建或删除 SAS 数据集、视图、索引等、更新已

2、存在的数据集、使得SAS 系统可以使用 SQL 语句、可以和 SAS 的数据步进行替换使用。注意， SQL 过程步并不是用来代替 SAS 数据步，也不是一个 客户化的报表工具，而是数据处理用到的查询工具。 1.2 SQL 过程步的特征 SQL 过程步并不需要对每一个查询进行重复、每条语句都是单独处理、不需要 print 过程步 就能打印出查询结果、 也不用 sort 过程步进行排序、 不需要 run 、要 quit 来结束 SQL 过程步 1.3 SQL 过程步语句 SELECT ：查询数据表中的数据 ALTER ：增加、删除或修改数据表的列 CREATE ：创建一个数据表 DELETE ：删

3、除数据表中的列 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 ：将数据集

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

5、idate 3 select Region, Product,Sales 4 from sashelp.shoes 5 where Region = Africa; NOTE: PROC SQL 语句有有效语法。 6 quit; 此外，我们还可以用 noexec 选项也可以用来进行语法测试。例： 7 proc sql noexec; 8 select Region, Product,Sales 9 from sashelp.shoes 10 where Region = Africa; NOTE: 由于 NOEXEC 选项，未执行语句。 11 quit; 这里提示未执行，未提示错误，说明该语句

6、没有语法错误。但是如果加入一个 sashelp.shoes 表里没有字段，这里就会出现错误，例： 12 proc sql noexec; 13 select Region, Product,Sales,test 14 from sashelp.shoes 15 where Region = Africa; ERROR: 以下这些列在起作用的表中没有找到 : test. 16 quit; 2.4 查询列 我们可以像 2.3 那样查询指定列，也可以用* 来查询所有列。例： proc sql; select * from sashelp.shoes; quit; 这里我们可以用 feedback 选

7、项来查看到底我们选择了哪些列： 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 消除重复值 我们可以用 distin

8、ct 选项来消除重复值。例如，我们要得到没有重复的所有地区的名称： proc sql; select distinct Region from sashelp.shoes quit; 2.6 where 子集查询 2.6.1 比较运算符 先列出 where 语句用到的比较运算符 LT 大于 EQ = 等于 LE = 大于或等于 NE A= 不等于 例如，我们要查询 sales 大于 100000 的所有数据： proc sql; select * from sashelp.shoes where sales100000 ; quit ; 262 in :只要满足in里的任意一个值，表达式即为真

9、，例如，我们要选择 和 Eastern Europe 的所有数据： proc sql; select * from sashelp.shoes where Region in (Africa,Eastern Europe); quit ; 2.6.3 逻辑运算符 OR | 或 AND select * from sashelp.shoes where Region in (Africa,Eastern Europe) and sales 100000; quit ; 2.6.4 CONTAINS 或?:判断某列是否包含指定字符串 例如，选择列 Region包含Afr的数据： proc sql;

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

11、egion= 进行替换。 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 ; 这里注意有两类通配符,%可以通配任意个任意字符

12、,_只能通配一个任意字符 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.shoes quit; 这时结果就会多一列 salesperstores用来得到

13、该地区该产品每个商店的平均销售量。这里要 注意的是，在创建表达式时，我们还可以在 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

14、 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 ; 这里提示一下，我们可以用任意多列进行排序，包括表达式结果（不

15、用calculated），但最 好是选择的列。 2.8.2 LABEL 与 FORMAT LABEL ：改变输出变量名的内容 FORMAT ：改变列的值的输出方式 例如，改变 salesperstores 的 label 禾口 format proc 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 ：均值 CO

16、UNT或N或FREQ:非缺失值个数 MAX ：最大值 MIN :最小值 NMISS :缺失值个数 STD:标准差 SUM :求和 VAR :方差 2.9.1 求和 sum proc sql; select Region, Product,Sales,Stores, sum(Sales,Inventory,Returns) as total from sashelp.shoes; quit ; 2.9.2 求均值 avg proc sql; select Region, Product,Sales,Stores, avg(Sales) as salesavg from sashelp.shoes

17、; quit ; 2.9.3 分组求均值 group by proc sql; select Region, avg(Sales) as salesavg from sashelp.shoes group by Region; quit ; 2.9.4 计数 count proc 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

18、group by Region having count(*) 50; quit ; 其它的就不多作介绍了，多用用就熟悉了 2.10 子查询 2.10.1找出regions平均sales大于全部平均 sales的region proc 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) 最终效果： 20

19、 ANY(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) 最终效果： 40 ALL (20,30,40) 最终效果： 20 例如，选择出 region为united state的sales小于所有region为af

20、rica的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 ：缺省选项，取消所有的更新或新插入的数据。

21、这里要注意的是，此操作不一 定完全能成功。 NONE ：阻止所有的与约束不符的更新或新数据 OPTOINAL ：取消所有可以成功取消的更新或新插入的数据 本功能用得很少，所以只是翻译一下，请大家查阅相关文献。 6 创建视图与索引 6.1 视图 视图最大的好处是它只是一个存储的查询， 因此不包含任何数据， 这可以减少磁盘的使用空 间。其它的功能与数据库的表类似。 创建视图语法： Create View View-name as Query-expression ; 例： proc sql noprint outobs=0; create View delayview as select * fr

22、om 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 colu mn- n ame=express

23、i on, Where expression; 注意，这里的 where 一定要写清楚，如果没有的话，则会更新所有的数据。 例： proc sql noprint; update delay set delaycat=Delay where delay= 0; quit ; 7.2 条件处理 用 CASE 语句来实现条件处理。例如，对 SASHELP.shoes 根据 sales 的大小进行分类： 语法： Select colu mn Case When when-condition then result-expression End; 例： proc sql; create table s

24、hoesrank as select *, (case when sales100000 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, Dro

25、p column-name,column- name, Modify column-definition,column- definition, 对已存在的数据表增加一个列：例： proc sql; alter table shoesrank add addcolumn num format=comma10.2, addcolumnmore cha r( 10 ); quit ; 从已存在的数据表中删除列，例： proc sql; alter table shoesrank drop addcolumn; quit ; 修改某列的属性，例： proc sql; alter table shoe

26、srank 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 ; SAS中的SQL语句完全教程之三：SQL过程步的其它特征 SAS 中的 SQL 语句完全教程之三

27、： SQL 过程步的其它特征 本系列全部内容主要以 SQL Processing with the SAS System (Course Notes) 为主进行讲解， 本书是在网上下载下来的， 但忘了是在哪个网上下的， 故不能提供下载链接了， 需要的话可 以发邮件向我索取， 我定期邮给大家， 最后声明一下所有资料仅用于学习， 不得用于商业目 的，否则后果自负。 前面两部分内容都比较简单， 本节内容才是本系列要介绍的重点。 不过这里装的内容都是点 到即止，如果以后有时间，会进行更详细地讲解。 1 SQL 过程步选项 SQL 过程步选项的作用主要是可以从更细节的方式去控制 SQL 过程步，并且可以

28、在不执行 过程的情况下对程序进行测试等。 下面介绍一下列出来的选项，这些选项大多经常用到，更多的选项可以参考 SAS 帮助。 INOBS ：进行一个查询时，对每个源数据表进行 N 行限制，仅对这 N 行的数据进行查询。 OUTOBS ：指定查询输出结果的观测数 LOOPS :指定SQL过程步内循环的次数(此选项我用得比较少，谁明白的可以讲一下，多 谢) NOPROMPT 和 PROMPT ：修改上述三个选项的效果， 从而让你选择是否继续或停止选项的 效果。 PRINT 和 NOPRINT ：控制是否打印选择的数据结果 NONUMBER 和 NUMBER ：控制是否在第一列打印观测值编号 DOU

29、BLE 和 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 sql inobs=10

30、; 238 create table tmp as 239 select a.*,b.* 240 from March a,Delay b 241 where a.flight=b.flight and a.date=b.date; WARNING: 变量 “ flight 已经”存在于文件 WORK.TMP 中 WARNING: 变量 date已经存在于文件 WORK.TMP 中。 WARNING: 变量 orig已经存在于文件 WORK.TMP 中。 WARNING: 变量 des已经存在于文件 WORK.TMP 中。 WARNING: Only 10 records were read

31、from WORK.DELAY （别名=B）由于 INOBS= 选项。 WARNING: Only 10 records were read from WORK.MARCH （别名=A ） 由于 INOBS= 选 项。 NOTE: 表 WORK.TMP 创建完成，有 7 行， 11 列。 1.3 outobs 选项 这个选项的功能与 inobs 相似，不同的是，这个选项指定结果的观测值数量，而不是读取源 表的数量，这在我们控制输出结果的数据量时非常有用。 243 proc sql outobs=10; 244 create table tmp1 as 245 select a.*,b.* 24

32、6 from March a,Delay b 247 where a.flight=b.flight and a.date=b.date; WARNING: 变量 “ flight已经存在于文件 WORK.TMP1 中 WARNING: 变量 date已经存在于文件 WORK.TMP1 中。 WARNING: 变量 orig已经存在于文件 WORK.TMP1 中。 WARNING: 变量 des已经存在于文件 WORK.TMP1 中。 WARNING: 语句由于 OUTOBS=10 选项而过早终止。 NOTE: 表 WORK.TMP1 创建完成，有 10 行，11 列。 1.4 prompt

33、选项 255 这里 prompt 选项，可以让你选择是否还是按照原来的设置进行动作，或者继续操作。如下 面的日志所以， 如果直接选择停止， 则得到 1.2 的结果， 如果一直按继续， 则得到如下结果： proc sql inobs=10 prompt; 256 create table tmp3 as 257 select a.*,b.* 258 from March a,Delay b 259 where a.flight=b.flight and a.date=b.date; 变量 “ flight 已经”存在于文件 WORK.TMP3 中 变量 date已经存在于文件 WORK.TMP3

34、 中。 变量 orig已经存在于文件 WORK.TMP3 中。 变量 des已经存在于文件 WORK.TMP3 中。 ORK TMP3 创建完成，有 19 行， 11 列 。 WARNING: WARNING: WARNING: WARNING: NOTE: 表 o 1.5 number 选项 proc sql outobs=4 number; select * from March; quit ; 1.6 reset 选项 对上述 SQL 语句加上 reset 选项，使其不输出序号 proc sql outobs=4 number; reset nonumber; select * from

35、 March; quit ; 2 DICTIONARY 2.1 DICTIONARY 介绍 DICTIONARY 可以得到很多 SAS 文件和会话等很多的元数据，包括 SAS 文件，外部文件， 系统选项、宏、标题、脚注等。 DICTIONARY 是在初始化时就创建，自动更新，并只允许读取操作，故不能修改。 下面列举一下 SAS 中 DICTIONARY 所包含的表： SAS V8 DICTIONARY 所包含的表：CATALOGS ： SAS 目录的信息 COLUMNS ： SAS 变量和列的信息 EXTFILES ：外部数据信息 INDEXES ：参与索引的列的信息 MACROS ：宏相关信

36、息 MEMBERS ：所有数据类型（表、视图、目录等）的信息 OPTIONS ：当前会话选项 STYLES ： ODS 的样式信息 TABLES ：表和数据集信息 TITLES ：标题和脚注信息 VIEWS ：视图信息 SAS V9 DICTIONARY 所包含的新的表： CHECK_CONSTRAINTS ： CHECK 约束信息 CONSTRAINT_COLUMN_USAGE ：约束列使用信息 CONSTRAINT_TABLE_USAGE ：约束表使用 DICTIONARIES ： DICTIONARY 所有表及其列 ENGINES ：可用的引擎 FORMATS ：可用的格式 GOPTIO

37、NS ： SAS/GRAPH 选项 LIBNAMES ： LIBNAME 信息 REFERENTIAL_CONSTRAINTS ：相关约束 REMEMBER ：已记录的信息 TABLE_CONSTRAINTS ：表约束 2.2 查看 2.1 中各表内容 查看各表的结构，以查看 dictionary.tables 为例： proc sql; describe table dictionary.tables; quit ; 10 条数据 这里可以从日志里看到表 dictionary.tables 的结构。 要查看这个表的数据，可以通过以下语句实现，这里我们只输出前 proc sql outobs=

38、10; select * from dictionary.tables; quit ; 2.3 使用 DICTIONARY 的信息 查看 SASHELP 库里的文件信息： proc sql; options nolabel nocenter; select memname,nobs,nvar,crdate from dictionary.tables where libname=SASHELP; quit ; 注意，这里的库名都是大写。 查看 SASHELP 库里有列名为 tabname 的所有表： proc sql; options nolabel nocenter; select memn

39、ame from dictionary.columns where libname=SASHELP and name=tabname; quit ; 注意这里是用 dictionary.columns 的信息 用 SASHELP.VMEMBER 来撮 DICTIONARY.MEMBERS 的信息： proc tabulate data=sashelp.vmember format= 8.; class libname memtype; keylabel N= ; table libname,memtype/rts= 10 misstext=None; run ; DICTIONARY 是一个非

40、常有用的信息库，本文主要是介绍一下其基本功能，以后有时间的 话会对其进行详细介绍。 3 SQL 过程步与宏语言的交互 SQL 过程步可以创建 SAS 宏变量，也可以生成一些自动的宏变量，从其中可以更好地理解 SQL 处理过程。 3.1 在 SQL 过程中使用宏变量 %let outputnumber=10; proc sql outobs= options nolabel nocenter; select * from dictionary.tables where libname=SASHELP; quit ; 这个很简单，就不讲了，下面讲一下本节的重点。 3.2 创建宏变量 SQL 过程步可以通过 into 语句来创建或更新宏变量，主要有三种方式。这里要注意的是， 在使用into语句创建宏变量时，我们是不能在create table或create view语句里使用的。当 我们不需要输出结果时，可以用 noprint 选项。 3.2.1 结果只有一行 Select col1,col2, In to : mvar1,:mvar2, From 例： proc sql noprint; selec