SAS中的SQL语句完全教程之一: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>
FROM from-list
<WHERE sql-expression_r>
<GROUP BY group-by-item <, ... group-by-item>>
<HAVING sql-expression_r>
<ORDER BY order-by-item <, ... 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 validate
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;
这里提示未执行,未提示错误,说明该语句没有语法错误。但是如果加入一个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选项来查看到底我们选择了哪些列:
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.shoes
quit; 2.6 where子集查询
2.6.1 比较运算符
先列出where语句用到的比较运算符:
LT < 小于
GT > 大于
EQ = 等于
LE <= 小于或等于
GE >= 大于或等于
NE ^= 不等于
例如,我们要查询sales大于100000的所有数据:
proc sql;
select *
from sashelp.shoes
where sales>100000;
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 sales>100000;
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.shoes
quit;
这时结果就会多一列salesperstores,用来得到该地区该产品每个商店的平均销售量。这里要注意的是,在创建表达式时,我们还可以在SQL里用到SAS中的除LAG和DIFF之外的所有函数。
这里我们还可以用表达式计算出来的结果来进行子集查询,但一定要记住用calculated关键词。例如我们要找出商店平均销售量大于5000的数据:
方法一:
proc sql;
select Region, Product,Sales,Stores,Sales/Stores as salesperstores
from sashelp.shoes
where Sales/Stores>5000;
quit;
方法二:
proc sql;
select Region, Product,Sales,Stores,Sales/Stores as salesperstores
from sashelp.shoes
where calculated salesperstores>5000;
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与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:均值
COUNT或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;
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
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
<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 Sales<any
(select Sales from sashelp.shoes where Region='Africa');
quit;
这个例子没有多少意义,只是说明一下any的用法
2.10.3 ALL关键词介绍
>ALL (20,30,40) 最终效果:>40
<ALL (20,30,40) 最终效果:<20
例如,选择出region为united state的sales小于所有region为africa的sales的数据
proc sql;
select Region,Sales
from sashelp.shoes
where Region='United States'
and Sales<all
(select Sales from sashelp.shoes where Region='Africa');
quit;
2.10.4 EXISTS与NOT EXISTS
proc sql;
select *
from sashelp.shoes
where exists
(select * from sashelp.orsales);
quit;
本系列全部内容主要以《SQL Processing with the SAS System (Course Notes)》为主进行讲解,本书是在网上下载下来的,但忘了是在哪个网上下的,故不能提供下载链接了,需要的话可以发邮件向我索取,我定期邮给大家,最后声明一下所有资料仅用于学习,不得用于商业目的,否则后果自负。
转载请注明出处:
http://blog.sina.com.cn/s/blog_5d3b177c0100cn8v.html 前面两部分内容都比较简单,本节内容才是本系列要介绍的重点。不过这里装的内容都是点到即止,如果以后有时间,会进行更详细地讲解。
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 sql inobs=10;
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: 变量“dest”已经存在于文件 WORK.TMP 中。
WARNING: On
ly 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 proc sql outobs=10;
244 create table tmp1 as
245 select a.*,b.*
246 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: 变量“dest”已经存在于文件 WORK.TMP1 中。
WARNING: 语句由于 OUTOBS=10 选项而过早终止。
NOTE: 表 WORK.TMP1 创建完成,有 10 行,11 列。
1.4 prompt选项
这里prompt选项,可以让你选择是否还是按照原来的设置进行动作,或者继续操作。如下面的日志所以,如果直接选择停止,则得到1.2的结果,如果一直按继续,则得到如下结果:
255 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;
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;
select * from March;
quit;
1.6 reset选项
对上述SQL语句加上reset选项,使其不输出序号
proc sql outobs=4 number;
reset nonumber;
select * from March;
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;
options nolabel nocenter;
select memname,nobs,nvar,crdate
from dictionary.tables
where libname='SASHELP';
quit;
注意,这里的库名都是大写。
查看SASHELP库里有列名为tabname的所有表:
proc sql;
options nolabel nocenter;
select memname
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是一个非常有用的信息库,本文主要是介绍一下其基本功能,以后有时间的话会对其进行详细介绍。
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,…
Into :mvar1,:mvar2,…
From …
例:
proc sql noprint;
select avg(sales),min(sales),max(sales)
into :mean, :min, :max
from sashelp.shoes;
quit;
%put &mean &min &max;
这种方式适合输出结果只有一行的情况。
3.2.2 提取前N行的值
Select a,b,…
Into :a1-:an, :b1-:bn
From …
这种方法前前N行的查询结果输入到一系列的N个宏变量中。
例:
proc sql noprint;
select Region,count(*) as frequency
into :Region1-:Region3, :freq1-:freq3
from sashelp.shoes
group by Region;
quit;
%put &Region1 &Region2 &Region3;
%put &freq1 &freq2 &freq3;
3.2.3 提取所有数据
Select col1,col2,…
Into :macrovar1, :macrovar2,…
Separated by ‘delimiter’
From …
这里将每列的所有数据都输出到一个宏变量中,并以指定的分隔符分开。
例:
proc sql noprint;
select distinct Region
into :RegionAll
separated by ','
from sashelp.shoes;
quit;
%put &RegionAll;
3.3 SQL过程步自动产生的宏变量
SQLOBS:记录输出结果或被删除的观测值个数
SQLRC:包含每个SQL语句的返回代码
SQLOOPS:包含SQL过程步内循环的次数
例:
proc sql;
select *
from sashelp.shoes;
quit;
%put &SQLOBS;
得到输出观测的条数。
4 程序测试与效率
这里主要介绍几个SQL过程步的选项:
Exec/Noexec:控制SQL语句是否执行
Nostimer/Stimer:在SAS日志中报告每个SQL语句的效率统计数据
Noerrorstop/Errorstop:批处理时控制当有错误发生时,是否要检查语法
例:noexe与feedback
66 proc sql feedback noexec;
67 select * 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;
NOTE: 由于 NOEXEC 选项,未执行语句。
68 quit;
NOTE: “PROCEDURE SQL”所用时间(总处理时间):
实际时间 0.01 秒
CPU 时间 0.00 秒
这里我们要注意的是,在比较不同SQL语句的效率时,一定要用CPU时间,而不是用实际时间,因为实际时间可能会因运行机器的环境而有很大的出入。还要注意的是,对于同一个数据集,在比较效率时,一定要考虑到该数据集第一次运行后其排序等已经发生变化,会对效率也有影响。
评论