第6章SQL过程

1、第第5章章 SQL过程过程sql过程概述 vSql过程是SAS内部的一个过程，实现对数据集或关系数据库的表进行操作的过程。 通过sql过程可以实现对数据集或关系数据库的表进行查询、修改、创建表、删除数据、插入数据和更新数据等功能。vSql过程是SAS内部已经编译好的一个过程，它实现了关系数据库的结构化查询功能，体现了SAS对大型数据库管理系统(如oracle,db2,sybase)通用的sql语言支持。Sql过程处理机制vSql过程可以对SAS系统的数据集、视图、关系数据库中的表进行操作处理，输出可以是数据集形式、视图形式、关系数据库中的表和报表等形式输出结果。Sql过程处理流程见图6-1.v

2、Sql 过程处理机制是对来自SAS数据集或关系数据库表的数据进行操作处理，通过sql结构查询语句处理后可生成SAS数据集、SAS视图、关系数据库表、v报表或赋值给宏变量。比较sql关系数据库表和SAS数据集 sql术语(关系数据库)SAS系统说明表SAS数据集数据集和表的结构相同，称呼不同行观测数据集一条记录称为观测，表中称为行列变量数据集称列为变量，表称为列SQL过程语法v语法格式: proc sql ；v 数据操作语句;v quit;Sql过程语法说明Sql过程操作语句 功能proc过程步开始关键字，调用过程语句sqlSAS内部编译好的过程名，执行sql语言选项Sql过程具有的设置参数，如

3、noprint数据操作语句关系数据库结构化语句或对数据集操作的语句，如查询quit告诉SAS系统sql过程步结束SQL过程特点v【注意】sql过程的结束语句标志为”quit”语句。vSql过程可以对SAS数据集操作，也可以对关系数据库中的表操作，功能强大，继承了关系数据库中的sql语言。vSql过程中的过程中的“选项选项”vSql过程中的选项是控制sql过程输出显示设置的应用，常用选项如下表所示：v表6-3 sql过程常用选项选项选项功功 能能print打印打印sql过程的输出到输出窗口，默认选项过程的输出到输出窗口，默认选项nonprint不打印不打印sql过程的输出到输出窗口过程的输出到输

4、出窗口number对输出观测记录指定行号对输出观测记录指定行号(row)nonnumber对输出观测记录不指定行号对输出观测记录不指定行号(row)，默认选项，默认选项Inobs=指定输入的观测记录行数，取正整数指定输入的观测记录行数，取正整数Outobs=指定输出的观测记录行数，取正整数指定输出的观测记录行数，取正整数Loops=指定指定sql过程内循环的最大次数过程内循环的最大次数double指定在各行插入一个空行指定在各行插入一个空行nodouble指定在各行不插入空行，默认选项指定在各行不插入空行，默认选项dquote对双引号中的内容而言，取对双引号中的内容而言，取ANSI表示为变量，

5、取表示为变量，取SAS表示为字表示为字符，默认值取符，默认值取SAS数据操作语句vSql过程数据操作语句是对数据集或关系数据库中的表进行操作的语句，可以实现创建数据集或表、创建索引、更新数据集或表等功能。v表6-4 常用数据操纵语句数据操作语句数据操作语句功功 能能create table创建关系数据库中的表或创建关系数据库中的表或SAS数据集数据集create index创建索引，对大表可以提高查询速度创建索引，对大表可以提高查询速度create view创建视图创建视图select查询关系数据库中的表或查询关系数据库中的表或SAS数据集的信息数据集的信息delete删除关系数据库中的表或删

6、除关系数据库中的表或SAS数据集中的行记录数据集中的行记录alter table对表或数据集的列变量进行修改、加入列或删除列对表或数据集的列变量进行修改、加入列或删除列drop删除表、视图或索引删除表、视图或索引Insert into插入观测记录插入观测记录update更新表或数据集中的对应列值更新表或数据集中的对应列值describe显示表或视图的定义信息显示表或视图的定义信息connect to建立与关系数据库的连接建立与关系数据库的连接disconnect from断开与关系数据库的连接断开与关系数据库的连接创建表语句与应用实例vSql过程可以通过create语句创建新数据集或连接数据库

7、在数据库里创建表，通过以下三种方式创建表。v1.基本语句创建表v基本语句创建表是根据数据属性定义表名、列名和列具有的属性。v语法格式： create table 表名 (column_1 属性，column_2 属性，column_3 属性n)v表6-5 基本语句创建表说明语句项语句项说说 明明 create table告诉告诉SAS系统创建表系统创建表表表 名名给创建的表起名字给创建的表起名字Column_1 至至column_n定义表中的列名定义表中的列名属性属性定义列名具有的数据类型，可取的属性如定义列名具有的数据类型，可取的属性如char、character、date、dec、deci

8、mal、float、int、integer、num、numeric、real等等vSAS系统通过基本语句创建表，实际是创建了一个新数据集，叫法不同意义相同，这个数据集只有数据集结构，也可以称为表结构，没有数据记录。查询语句创建表v通过查询语句创建表实际上是复制查询语句指定列的列名和对应列的数据到新表中，相当于数据集的复制，这与数据步中复制数据集相比更灵活，可以选择任意列。查询语句创建表的语法格式vCreate table 表名 as select column 1, column 2, column n from 表名 ；vas: 关键字，必选项。vselect column 1, colum

9、n 2, column n from 表名:v查询语句，建立的新表取查询语句中的字段。v：根据某条件查询符合要求的数据复制到v所创建的新表中。注意v如果只需要查询表的表结构，则只需在查询语句中加入“where 1=2”条件语句，告诉SAS系统只复制表结构。v语法：create table 表名 as select column 1,vColumn 2,column n from 表名 where 1=2;Like 语句创建新表结构v语法格式： create table 表名 like 已存在表名;vLike:关键字，表示只复制表结构。v已存在表名: 已经建立的表名。v【注意】like语句只是创

10、建表结构，不复制数据集数据。创建索引语句与应用实例v对于数据量比较大的表，为提高查询速度，sql过程中可以通过创建索引语句对大数据集或关系数据库中的表创建索引。v【注意】创建的索引是要占用存储空间的，需要定期维护成本，对于数据量比较小的表和经常对表进行插入、更新或删除操作的表，尽量不要创建索引。创建索引要具体情况具体分析，并不是什么表度适合创建索引。对于与外部关系数据库结合的，要先建立逻辑库或连接语句建立SAS与关系数据库的连接通道，才能对关系数据库中的表创建索引。创建简单索引v对表创建简单索引，是指只对表中的一个列创建索引，此索引的索引名必须与列名相同。v语法格式：create index

11、索引名 on 表名(列名)；v【注意】索引名必须以列名命名，只取表中的一个列。建立复合索引v所谓复合索引是指对一个表中的多个列建立索引，此索引的索引名不依赖单个列，可以自己定义索引名。v语法格式: create index 索引名 on 表名 (column 1,);v【注意】创建复合索引时对所要建立索引的v表至少取两个列。创建视图语句与应用实例v对于一些机密数据有些语句可以给用户看，有些不需要用户看到，可以通过视图访问，将用户需要的查询信息封装到视图表中。视图其实是一张虚表，不占用存储空间，具有表的特性。语法格式vCreate view 视图名 AS 查询语句 ;vCreate view:

12、创建视图的关键语句。v视图名:认为定义的视图名称，遵守变量命名规范。vAs: 关键字。v查询语句：select语句的应用。v: 查询的语句是外部关系数据库中的表，需要通过此逻辑库语句建立SASv与关系数据库的连接。1.对查询数据集信息建立视图v为节省存储，可以对大数据量的查询建立视图形式的访问形式。v语法格式： create view 视图名 as select column 1, column 2,column n from 数据集名 ；v【注意】where语句的过滤条件遵守where条件的语法。2.访问外部关系数据库创建视图v对于访问外部关系数据库中的数据信息，需要先通过SAS逻辑库这座桥

13、，建立SAS与关系数据库的连接。v语法格式：create view 视图名 asvSelect column 1，column 2,column n fromv逻辑库名.表名 vUsing libname 逻辑库名 oracle user=登录数据库用户名 pass=登录数据库密码 path=数据库实例;v【语法解读】通过using语句中指明的逻辑库信息，建立与oracle数据库的连接，通过二级连接方式读取所登录的数据库中的表，以此查询信息建立视图。查询语句与应用实例v查询语句是sql语言中使用频率最高的，也是最灵活、最具有发挥思维的语言。Select语句可以实现对数据集和关系数据库中的表进行

14、数据查询，查询语句中可以使用函数，可以对单表和多表进行组合查询，同时查询语句中还可以使用子查询。v语法格式：Select object-itme into macro_variablefrom from_list ;语法解读v查询语句查询表或视图的所有信息，语法格式为：vSelect * from from_list v查询语句查询表或视图指定列消息，若去掉重复记录可在列名前加“distinct”关键字，语法格式为：vSelect column 1 from_list ；v表 6-6 from_list 可取选项From_list可取选项可取选项 说说 明明表名表名 表表别名别名指定查询表名，

15、可以通过指定查询表名，可以通过as语句给表指语句给表指定别名定别名视图名视图名视图别名视图别名指定查询视图名，可以通过指定查询视图名，可以通过as语句给视语句给视图指定别名图指定别名Join_table指定表与表的关联，如内连接、左连接和指定表与表的关联，如内连接、左连接和自连接自连接Connectionto指定与关系数据库表的连接指定与关系数据库表的连接v表 6-7 查询条件可取选项v【注意】查询条件有严格的顺序，如果有多个条件，按上面所列顺序执行。查询语句只在最后语句结束时加一个分号“；”，表示查询语句结束。查询条件可取选项查询条件可取选项 说说 明明where指定查询语句的过滤条件指定查

16、询语句的过滤条件Group by指定查询表或视图的分组列指定查询表或视图的分组列having根据指定条件对数据分组，与根据指定条件对数据分组，与group by一起使用一起使用Order by指定排序列，可以降序也可以升序，默认升序指定排序列，可以降序也可以升序，默认升序单表查询v只对一个表通过查询语句查询表或视图的信息.v语法格式：select | from 表名|视图名；v【注意】|：只能取其一，查询整个表或视图取 “*”,按指定列名查询时去掉“*”,为互斥项。Where语句vWhere语句属于过滤语句，对查询语句根据where语句指定的条件过滤数据，选择符合条件的数据输出。v语句语法格式

17、：where 条件语句Group by语句vGroup by 语句指定分组列，对数据集及表中的记录按某一列分组，同时可以使用汇总函数对分组汇总，多个分组列以空格分隔。v语法格式： group by 列名 1Having语句vHaving语句和where语句很相似，having语句可以实现对分组的数据按条件过滤，一般和group by 语句一起使用，放在group by 语句后面。如果没有group by 语句， 此处having语句就相当于where语句的功能。Having语句和where语句区别Having语句Where语句对所分组设置条件对数据集或表中数据设置过滤条件与group by语句

18、连用时放其后面与group by语句连用时放其前面无group by语句时与where语句功能相同Group by语句对where语句没有限制Order by 语句vOrder by 语句对查询数据集或视图信息按order by 语句指定列降序或升序输出查询信息。v语法格式：order by 列名 1vv【注意】默认是“asc”升序，降序把列指定为“desc”,每个order by语句中指定的列可以设置升/v 降序，只取其中一个排序方式。多表查询v多表查询实际是查询语句一次对多个表或数据集进行查询操作，可以根据需求实现表关联的查询，表与表的自连接、左连接、右连接和内连接等方式。常用连接方式连接

19、方式连接方式 说说 明明自连接表或数据集自身和自身连接，通过起别名方式内连接表或数据集根据条件实现内部关联连接，自连接属于此类外连接包括左外连接、右外连接和全外连接其他连接如cross连接、union连接和natural连接多个查询的合并v对于多个查询语句查询输出的信息，可以通过合并语句对查询信息进行合并，生成一个数据集或表。v多个查询的结果可以合并生成新的查询结果输出。多个查询输出合并语句语句语句 功功 能能union多个查询输出结果通过union合并查询，去掉重复数据intersect求多个查询结果的交集，即公共部分except取查询结果的差集，输出结果为第一个查询去掉第二个查询的结果，可

20、以把公共部分过滤掉Outer union对多个查询输出结果横向合并，横向合并查询输出。删除语句vSql结构化查询语言通过delete 语句可以删除数据集或表中的数据，通过drop 语句可以删除表、索引或视图.v【注意】delete 语句只是删除表或数据集中的数据，表结构是存在的，而drop语句删除表、视图或索引时是全删除，数据和表结构都被删除，不能恢复。deleteDelete语句只是删除数据集或表中的记录语句只是删除数据集或表中的记录dropDrop语句删除表、视图或索引，是全删除，不能恢复Delete语句vDelete语句一般后面跟where语句，根据where语句中的条件删除观测记录。v

21、语法格式： delete from 表名 ;vDelete from: 删除语句的关键字，必选项。v表名：要删除的表名称。v: 根据条件删除表中的记录，可选项，不加时删除表中所有的记录。Drop语句vDrop语句删除表、视图或索引时是全删除，数据和表结构都被删除，删除后无法恢复，用词语句时一定要慎重。v语法格式： drop table 表名|视图名|索引;vDrop table: 删除语句的关键字。v表名|视图名|索引：这三个选项根据需求选择。修改表语句vSql结构化查询语言中修改表语句可以更灵活地修改表中字段的属性，实现给列改名称、加约束、删除列、删除外键、删除主键等功能。v语法格式：alt

22、er table 表名 选项语句;v修改表语句根据需求可以在“alter table 表名”v语句后选择下表所列选项语句。v表6-14 选项语句选项语句选项语句 功功 能能Add constraint 约束名 条件为表加入约束，如主键约束、外键约束Add 新列名 定义类型 其他条件 为表加入一个新列，新列要定义列类型Drop constraint 约束名删除表中的约束，多个约束以逗号分隔Drop 列名删除表中指定的列，多个列用逗号分隔Drop foreign key 外键约束名删除表中具有的外键约束Drop primary key cascade删除表中具有的主键Modify 列名 修改类型定

23、义对表中的列可以修改输入格式和输出格式，可以加入标签，不能改列名插入语句vSql过程可以通过insert into 语句向数据集或关系数据库中的表插入数据。v语法格式: insert into 数据集名| 数据库中表名v(对应变量名) values (插入的对应变量数据);vInsert into:插入语句关键字。v数据集名|数据库中表名：任选其一，如果是数据库中表名，要建立数据库连接逻辑库。vValues:插入语句值关键字。v插入的对应变量数据：指定插入的数据值。更新语句vSql过程可以通过update语句根据条件更新符合条件的数据集或关系数据库中表的记录。v语法格式: update 表名

24、set clomn_1= 或;vUpdate: 关键字，必选项。v表名：要更新的表名称。vSet: 关键字，必选项。vClomn_1= 或：更新列名或对应的值，如果同一个条件更新多个列，列与列之间用逗号分隔就可以。v: 可选项，根据条件更新时需要加条件语句.显示表定义信息语句v对创建好的表、数据集或视图，可以通过describe语句显示表的定义信息。v语法：describe table 表名|数据集名|视图名;vdescribe table:显示表、数据集或视图定义信息的关键字语句。v表名|数据集名|视图名：显示对象，任取一类。SAS与关系数据库的连接和断开语句vSAS除了通过逻辑库方式连接o

25、racle数据库外，还有另一种方式，通过connect to语句连接数据库，即通过disconnect from语句断开与关系数据库的连接。v连接语法格式：Connect to oracle (连接数据库定义语句)v断开数据库连接语法格式：disconnect from oracle;语法解读vConnect to: 连接数据语句关键字，后面的oracle是告诉SAS系统连接的是oracle库引擎。v连接数据库定义语句：指登录数据库的信息，如用户名、密码、数据库实例。vDisconnect from oracle:指明与当前oracle数据库断开交互。Sql过程与oracle数据库连接vSql

26、过程无论连接哪个类型的数据库，都需要v通过定义逻辑库或定义连接数据库的宏变量作为桥梁，实现sql过程与数据库的沟通。vSAS建立与oracle 数据库的连接接口可以通过创建逻辑库或定义宏变量两种方式。此处v建立的逻辑库或定义的宏变量实现了SAS与oracle 数据库连接的通道。SAS建立与oracle数据库连接方式v1.建立逻辑库连接数据库语法vLibname 逻辑库名 oracle user=登录数据库用户名 password=登录数据库密码 path=数据库实例;v2.定义宏变量连接数据库语法v%let 宏变量=user=登录数据库用户名 password=登录数据库密码 path=数据库

27、实例；SAS建立与oracle数据库连接方式v创建SAS逻辑库连接oracle数据库接口：vLibname dz oracle user=chiran password=chiran path=orcl;v/*建立与数据库连接的逻辑库*/v定义宏变量创建SAS与oracle数据库连接的接口：v% let dz_cnt=user=chiran password=chiran path=orcl;v/*定义宏变量建立与数据库连接的变量*/Select语句综合应用vSAS系统中sql过程中select语句查询关系数据库oracle中的表信息，是先通过逻辑库或数据库连接宏变量建立与关系数据库oracl

28、e的连接，然后SAS系统的select语句交给关系数据库执行，实现对数据库中表的信息查询。可以根据条件实现查询，满足业务需求。在关系数据库中，select语句是必须掌握ide，通过select语句的灵活运用可以实现对复杂业务的处理。数据库表中的字段值赋值给定义的宏变量vSAS语言中定义宏变量可以把从数据库中取出的数据存储到宏变量中，通过宏变量可以利用数据库中的数据。v宏变量定义：%let 宏变量名;v查询语句中引用宏变量语句： :宏变量名v【注意】查询语句中引用宏变量名要加冒号“:”数据库查询出来的值赋值给数据集中的变量数据库查询出来的值赋值给数据集中的变量v数据集中的变量引用数据库中变量数据的具体方法如下：v(1)通过sql过程中的select语句把数据库中查询出来的变量数据赋值给宏变量。v(2)在数据集中通过length语句定义新的变量，v语法格式为：length 变量名 定义变量类型;v(3)通过“&”符号引用宏变量，赋值给length语句中定义的宏变量，语法格式为：v变量名=&宏变量名;注意v数据如果为日期类型，引用宏变量值要加双引号“，”同时加字母d(d取date的第一个字母)，dt表示日期时间。v变量名=“&宏变量名”Sql过程索引应用v在关系数据库中查询大表需要建立索引提高查询速度，对于关系数据库中的分区表可以根据分区建立索引，实现按分区