来自 http://blog.sina.com.cn/supersasmacro
第一部分 SQL简介与基本查询功能
本系列全部内容主要以《SQL Processing with the SAS System (Course Notes)》为主进行讲解,本书是在网上下载下来的,但忘了是在哪个网上下的,故不能提供下载链接了,需要的话可以发邮件向我索取,我定期邮给大家,最后声明一下所有资料仅用于学习,不得用于商业目的,否则后果自负。
转载请注明出处:http://blog.sina.com.cn/s/blog_5d3b177c0100cksl.html
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 <DISTINCT> object-item <, …object-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
2
3
4
5
NOTE: PROC SQL 语句有有效语法。
6
此外,我们还可以用noexec选项也可以用来进行语法测试。例:
7
8
9
10
NOTE: 由于 NOEXEC 选项,未执行语句。
11
这里提示未执行,未提示错误,说明该语句没有语法错误。但是如果加入一个sashelp.shoes表里没有字段,这里就会出现错误,例:
12
13
14
15
ERROR: 以下这些列在起作用的表中没有找到: test.
16
2.4 查询列
我们可以像2.3那样查询指定列,也可以用*来查询所有列。例:
proc sql;
quit;
这里我们可以用feedback选项来查看到底我们选择了哪些列:
17
18
19
NOTE: Statement transforms to:
20
这时,我们可以看到从sashelp.shoes表中选择了8个列
2.5 消除重复值
我们可以用distinct选项来消除重复值。例如,我们要得到没有重复的所有地区的名称:
proc sql;
quit;
2.6 where子集查询
2.6.1 比较运算符
先列出where语句用到的比较运算符:
LT
GT
EQ
LE
GE
NE
例如,我们要查询sales大于100000的所有数据:
proc sql;
quit;
2.6.2 in:只要满足in里的任意一个值,表达式即为真,例如,我们要选择Region在Africa和Eastern Europe的所有数据:
proc sql;
quit;
2.6.3 逻辑运算符
OR
AND
NOT
例如,选择Region在Africa和Eastern Europe,且销售额大于100000的所有数据:
proc sql;
quit;
2.6.4 CONTAINS或?:判断某列是否包含指定字符串
例如,选择列Region包含’Afr’的数据:
proc sql;
quit;
2.6.5 IS NULL或IS MISSING:判断某列数据是否为空
例如,如果找出Region为空的数据:
proc sql;
quit;
注意,这里我们还可以用以下表达式对where语句进行替换。如果region为数值型变量,则可以用region=.,如果region为字符型变量,则可以用region= ‘’进行替换。
2.6.6 Between and:选择某一区间的数据
例如选择sales大于100000,但小于200000的所有数据:
proc sql;
quit;
2.6.7 like:判断是否能匹配某些字符
例如,选择以region以A开头的所有地区
proc sql;
quit;
这里注意有两类通配符,‘%’可以通配任意个任意字符,‘_’只能通配一个任意字符
2.6.8 =*:类似匹配
这里由于sashelp.shoes里没有符合要求的数据,所有就用书上的例子说明一下吧:
Where lastname=* ‘smith’,出来的结果可能是:smith,smythe等
2.7 表达式
我们可以通过已有的列进行计算来得到新的列,这时用关键词as来给新的列赋列名,例如:
proc sql;
quit;
这时结果就会多一列salesperstores,用来得到该地区该产品每个商店的平均销售量。这里要注意的是,在创建表达式时,我们还可以在SQL里用到SAS中的除LAG和DIFF之外的所有函数。
这里我们还可以用表达式计算出来的结果来进行子集查询,但一定要记住用calculated关键词。例如我们要找出商店平均销售量大于5000的数据:
方法一:
proc sql;
quit;
方法二:
proc sql;
quit;
2.8 查询结果展示
2.8.1 order by数据排序
默认的排序方式是升序,我们可以用DESC关键词来进行降序排列。例如以sales降序排列数据:
proc sql;
quit;
这里提示一下,我们可以用任意多列进行排序,包括表达式结果(不用calculated),但最好是选择的列。
2.8.2 LABEL与FORMAT
LABEL:改变输出变量名的内容
FORMAT:改变列的值的输出方式
例如,改变salesperstores的label和format
proc sql;
quit;
2.9 处理SQL常用函数
MEAN或AVG:均值
COUNT或N或FREQ:非缺失值个数
MAX:最大值
MIN:最小值
NMISS:缺失值个数
STD:标准差
SUM:求和
VAR:方差
2.9.1 求和sum
proc sql;
quit;
2.9.2 求均值avg
proc sql;
quit;
2.9.3 分组求均值group by
proc sql;
quit;
2.9.4 计数count
proc sql;
quit;
2.9.5 HAVING数据子集
proc sql;
quit;
其它的就不多作介绍了,多用用就熟悉了
2.10子查询
2.10.1 找出regions平均sales大于全部平均sales的region
proc sql;
quit;
2.10.2 ANY关键词介绍
>ANY(20,30,40)
<ANY(20,30,40)
=ANY(20,30,40)
例如,选择出region为united state的sales小于任意region为africa的sales的数据
proc sql;
quit;
这个例子没有多少意义,只是说明一下any的用法
2.10.3 ALL关键词介绍
>ALL (20,30,40)
<ALL (20,30,40)
例如,选择出region为united state的sales小于所有region为africa的sales的数据
proc sql;
quit;
2.10.4 EXISTS与NOT EXISTS
proc sql;
quit;
第二部分 数据合并与建表、建视图
1 连接joins分为内连接inner joins和外连接outer joins
内连接:仅返回匹配的数据,最多可以有32个表同时进行内连接
外连接:返回所有匹配的数据和非匹配的数据,一次只能有两个表或视图进行外连接
迪卡尔积:返回表内所有可能的匹配情况。例如表A有10*20的数据,表B有30*40的数据,则两个表的迪卡尔积有(10+30)*(20+40)=40*60的数据
我们先建立两个数据集:
data march;
219
622
132
271
302
114
202
219
132
202
219
622
271
202
219
622
132
271
302
114
202
219
622
132
271
114
202
219
132
302
114
132
271
302
;
run;
data delay;
114
202
622
132
302
114
202
219
622
132
271
302
114
202
219
622
132
271
302
114
202
219
132
302
622
132
271
302
;
run;
1.1 内连接
proc sql;
quit;
1.2 外连接
1.2.1 左连接left join
proc sql;
quit;
1.2.2 右连接right join
proc sql;
quit;
1.2.3 全连接full join
proc sql;
quit;
1.3 迪卡尔积
proc sql;
quit;
这里再大概说明一下内外连接的实现的基本原理:首先生成两个数据表的迪卡尔积,然后再根据where语句来选择符合条件的数据作为输出结果。当然,在实际处理过程中,SQL过程步会对这个迪卡尔积的数据集进行优化,将其划分成小块数据进行处理。
2 复杂的连接
这里因为找不到很好的例子来说明如何处理复杂的连接,因此就不讲解了,大概说一下思路吧:简单地说,对于复杂的查询,我们应该将该查询分解成几个小的子查询,然后对每个子查询的结果进行测试,最后将所有的子查询结合起来就组成了这个复杂的查询。这样会比一来就写复杂的查询容易得多。
3 数据集SET操作
这里有四类SET操作,分别是EXCEPT、INTERSECT、UNION、OUTER UNION。
EXCEPT:得到除第二个数据集以外的所有第一个数据集里的数据
INTERSECT:得到第一个数据集和第二个数据集都有的数据
UNION:得到两个数据集所有的数据,这里如果两个数据集有相同的数据,重复数据只出现一次
OUTER UNION:得到两个数据集所有的数据。
还有两个关键词来修改SET操作的行为:ALL和CORRESPONDING
ALL:并不删除重复值,不能与OUTER UNION合用(注意,用ALL关键词,一种情况是你不管是否会有重复值,另一个情况是不可能出现重复值,例如有主键的数据)
CORRESPONDING:只保留两个数据集都有的字段,一般简写为CORR
3.1 EXCEPT
找出所有没有延迟的航班和日期:
proc sql;
quit;
注意:这里重复值已经被删除。如果要得到所有的包含重复值的数据,就要用到关键词ALL:
proc sql;
quit;
如果只保留两个数据集都有的字段,则用到关键词CORRESPONDING(可简写为CORR):
proc sql;
quit;
3.2 INTERSECT
找出所有延迟的航班和日期:
proc sql;
quit;
关键词ALL和CORRESPONDING与在EXCEPT中一样,这里不再作介绍
3.3 UNION
选择所有的数据,这里的重复值只出现一次
proc sql;
quit;
关键词ALL和CORRESPONDING与在EXCEPT中一样,这里不再作介绍
3.4 OUTER UNION
proc sql;
quit;
这里,如果我们用到关键词CORR,就可以将相同字段的数据结合到一起:
proc sql;
quit;
4 建表
4.1 建空表
4.1.1 通过指定变量建空表
proc sql noprint;
quit;
4.1.2 拷贝数据集来建空表
proc sql noprint;
quit;
4.1.3 查询语句中,用outobs选项来建空表
proc sql noprint outobs=0;
quit;
4.2 对数据表增加数据
主要有三种方法:
4.2.1 SET语句
proc sql noprint;
insert into flight114
set flight=’302′ ,
quit;
4.2.2 VALUES语句
proc sql noprint;
insert into flight114
values(‘271’,’07MAR94’d,’LGA’,’PAR’,’1-10 Minutes’,’International’,4);
quit;
4.2.3 条件查询
proc sql noprint;
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;
CONSTRAINT percent _check check
(percent le 1.0));
quit;
这时,如果我们插入的数据中,percent>1的话,将会出现错误。
5.2 回滚ROLLBACKS
当我们用INSERT或UPDATE的时候,操作要等到发生错误的时候才会停止,这时会出现一个问题,就是数据表的一些数据更新了,而另一些数据没更新,这时如果我们要回到原来的状态,就需要用到UNDO_POLICY选项进行回滚。
UNDO_POLICY有三个选项:
REQUIRED:缺省选项,取消所有的更新或新插入的数据。这里要注意的是,此操作不一定完全能成功。
NONE:阻止所有的与约束不符的更新或新数据
OPTOINAL:取消所有可以成功取消的更新或新插入的数据
本功能用得很少,所以只是翻译一下,请大家查阅相关文献。
6 创建视图与索引
6.1 视图
视图最大的好处是它只是一个存储的查询,因此不包含任何数据,这可以减少磁盘的使用空间。其它的功能与数据库的表类似。
创建视图语法:
Create View View-name as
例:
proc sql noprint outobs=0;
quit;
6.2 索引
语法:
Create <unique> Index index-name
On table-name (column-name,column-name);
视图和索引都用得很少,所以这里就不作讲解了,大家查阅相关文献吧。
7 维护表
这里主要讲一下如何更新或删除已存在的表的数据,如何对一个表增加、减少、或改变其列的属性,如何删除表、视图和索引。
7.1 更新数据
这里主要用update来更新表的数据,语法如下
Update table-name
Set column-name=expression,…
Where expression;
注意,这里的where一定要写清楚,如果没有的话,则会更新所有的数据。
例:
proc sql noprint;
update delay
set delaycat=’Delay’
where
quit;
7.2 条件处理
用CASE语句来实现条件处理。例如,对SASHELP.shoes根据sales的大小进行分类:
语法:
Select column…
Case <case-operand>
When when-condition then result-expression
< When when-condition then result-expression>
<else result-expression >
End;
例:
proc sql;
quit;
7.3 删除行
语法:
Delete from table-name
Where expression;
例:
proc sql;
Delete from shoesrank
Where Region=’Africa’;
quit;
7.4 改变列
语法:
Alter Table table-name
对已存在的数据表增加一个列:例:
proc sql;
alter table shoesrank
add addcolumn num format=comma10.2,
addcolumnmore char(10);
quit;
从已存在的数据表中删除列,例:
proc sql;
alter table shoesrank
drop addcolumn;
quit;
修改某列的属性,例:
proc sql;
alter table shoesrank
modify
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;
第三部分 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
1.1 double选项
proc sql
quit;
1.2 inobs选项
注意:这里inobs选项只读取每个源表前10条数据进行后续的操作,如下面的日志所示。两个表都只读取10条数据进行关联,最后得到7条关联好的数据。
237
238
239
240
241
WARNING: 变量“flight”已经存在于文件 WORK.TMP 中。
WARNING: 变量“date”已经存在于文件 WORK.TMP 中。
WARNING: 变量“orig”已经存在于文件 WORK.TMP 中。
WARNING: 变量“dest”已经存在于文件 WORK.TMP 中。
WARNING: Only 10 records were read 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
244
245
246
247
WARNING: 变量“flight”已经存在于文件 WORK.TMP1 中。
WARNING: 变量“date”已经存在于文件 WORK.TMP1 中。
WARNING: 变量“orig”已经存在于文件 WORK.TMP1 中。
WARNING: 变量“dest”已经存在于文件 WORK.TMP1 中。
WARNING: 语句由于 OUTOBS=10 选项而过早终止。
NOTE: 表 WORK.TMP1 创建完成,有 10 行,11 列。
1.4 prompt选项
这里prompt选项,可以让你选择是否还是按照原来的设置进行动作,或者继续操作。如下面的日志所以,如果直接选择停止,则得到1.2的结果,如果一直按继续,则得到如下结果:
255
256
257
258
259
WARNING: 变量“flight”已经存在于文件 WORK.TMP3 中。
WARNING: 变量“date”已经存在于文件 WORK.TMP3 中。
WARNING: 变量“orig”已经存在于文件 WORK.TMP3 中。
WARNING: 变量“dest”已经存在于文件 WORK.TMP3 中。
NOTE: 表 WORK.TMP3 创建完成,有 19 行,11 列。
1.5 number选项
proc sql outobs=4 number;
quit;
1.6 reset选项
对上述SQL语句加上reset选项,使其不输出序号
proc sql outobs=4 number;
reset nonumber;
quit;
2 DICTIONARY
2.1 DICTIONARY介绍
DICTIONARY可以得到很多SAS文件和会话等很多的元数据,包括SAS文件,外部文件,系统选项、宏、标题、脚注等。
DICTIONARY是在初始化时就创建,自动更新,并只允许读取操作,故不能修改。
下面列举一下SAS中DICTIONARY所包含的表:
SAS V8 DICTIONARY所包含的表:
CATALOGS:SAS目录的信息
COLUMNS:SAS变量和列的信息
EXTFILES:外部数据信息
INDEXES:参与索引的列的信息
MACROS:宏相关信息
MEMBERS:所有数据类型(表、视图、目录等)的信息
OPTIONS:当前会话选项
STYLES:ODS的样式信息
TABLES:表和数据集信息
TITLES:标题和脚注信息
VIEWS:视图信息
SAS V9 DICTIONARY所包含的新的表:
CHECK_CONSTRAINTS:CHECK约束信息
CONSTRAINT_COLUMN_USAGE:约束列使用信息
CONSTRAINT_TABLE_USAGE:约束表使用
DICTIONARIES:DICTIONARY所有表及其列
ENGINES:可用的引擎
FORMATS:可用的格式
GOPTIONS:SAS/GRAPH选项
LIBNAMES:LIBNAME信息
REFERENTIAL_CONSTRAINTS:相关约束
REMEMBER:已记录的信息
TABLE_CONSTRAINTS:表约束
2.2 查看2.1中各表内容
查看各表的结构,以查看dictionary.tables为例:
proc sql;
describe table dictionary.tables;
quit;
这里可以从日志里看到表dictionary.tables的结构。
要查看这个表的数据,可以通过以下语句实现,这里我们只输出前10条数据
proc sql outobs=10;
select * from dictionary.tables;
quit;
2.3 使用DICTIONARY的信息
查看SASHELP库里的文件信息:
proc sql;
quit;
注意,这里的库名都是大写。
查看SASHELP库里有列名为tabname的所有表:
proc sql;
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是一个非常有用的信息库,本文主要是介绍一下其基本功能,以后有时间的话会对其进行详细介绍。
3 SQL过程步与宏语言的交互
SQL过程步可以创建SAS宏变量,也可以生成一些自动的宏变量,从其中可以更好地理解SQL处理过程。
3.1 在SQL过程中使用宏变量
%let outputnumber=10;
proc sql outobs=&outputnumber;
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,…
例:
proc sql noprint;
quit;
%put &mean &min &max;
这种方式适合输出结果只有一行的情况。
3.2.2 提取前N行的值
Select a,b,…
这种方法前前N行的查询结果输入到一系列的N个宏变量中。
例:
proc sql noprint;
quit;
%put &Region1 &Region2 &Region3;
%put &freq1 &freq2 &freq3;
3.2.3 提取所有数据
Select col1,col2,…
这里将每列的所有数据都输出到一个宏变量中,并以指定的分隔符分开。
例:
proc sql noprint;
quit;
%put &RegionAll;
3.3 SQL过程步自动产生的宏变量
SQLOBS:记录输出结果或被删除的观测值个数
SQLRC:包含每个SQL语句的返回代码
SQLOOPS:包含SQL过程步内循环的次数
例:
proc sql;
quit;
%put &SQLOBS;
得到输出观测的条数。
4 程序测试与效率
这里主要介绍几个SQL过程步的选项:
Exec/Noexec:控制SQL语句是否执行
Nostimer/Stimer:在SAS日志中报告每个SQL语句的效率统计数据
Noerrorstop/Errorstop:批处理时控制当有错误发生时,是否要检查语法
例:noexe与feedback
66
67
NOTE: Statement transforms to:
NOTE: 由于 NOEXEC 选项,未执行语句。
68
NOTE: “PROCEDURE SQL”所用时间(总处理时间):
这里我们要注意的是,在比较不同SQL语句的效率时,一定要用CPU时间,而不是用实际时间,因为实际时间可能会因运行机器的环境而有很大的出入。还要注意的是,对于同一个数据集,在比较效率时,一定要考虑到该数据集第一次运行后其排序等已经发生变化,会对效率也有影响。