SAS统计分析软件SQL从单个表中检索数据.ppt

1、第20章 SQL从单个表中检索数据,清华大学经管学院 朱世武,SELECT语句综述,SELECT语句是PROC SQL的主要工具。使用SELECT语句可以识别、检索和操作表中的数据，使用SELECT子句可以设定查询条件。,SELECT语句格式,SELECT object-item FROM from-list ;,SELECT 子句,语句格式 SELECT object-item FROM from-list,选择所有列,语句格式： COLUMN-NAME= * (asterisk) select * from from-list SELECT子句中，“*”号表示选择表中的所有列。,例：pro

2、c sql outobs=3; select * from ResDat.dret;,选择特定列,语句格式： Select column-name from from-list,例20.1 选择特定列。 proc sql outobs=3; title 股票代码; select stkcd from resdat.lstkinfo; proc sql outobs=3; title 股票代码和名称; select stkcd,lstknm from resdat.lstkinfo; quit;,用DISTINCT语句剔除查询结果中重复观测,语句格式： =distinct,例20.2 删除重复观

3、测。 proc sql; select distinct stkcd from ResDat.yrret; quit;,创建说明列,语句格式：SELECT TEXT， COLUMN-NAME,例20.4 创建说明列。 proc sql outobs=3; select Stock Code for, lstknm, is, stkcd from ResDat.lstkinfo; quit;,输出窗口结果：,计算新列值,语句格式：Calculating Values =Calculating Expression of columns,例20.5 计算股票每日成交金额。 proc sql out

4、obs=3; title Trading Sum; select stkcd, lstknm, clpr*trdvol format=12.2 from ResDat.qttndist; quit;,为列分配别名,语句格式：SELECT calculation-form 别名必须符合SAS名称要求，别名只在当前的查询中有效。,例20.6 为列分配别名。 proc sql outobs=3; title Trading Sum; select stkcd, lstknm, clpr*trdvol as trdsum format=12.2 from ResDat.qttndist; quit;,

5、CALCULATED 语句,语句格式：Calculated Column-name 使用别名引用一个计算过的列值时，必须使用Calculated 关键词，并将Calculated放在列名称之前，以此告知PROC SQL这个列是经计算得到的。,proc sql outobs=3; select stkcd,lstknm,date,clpr*mcfacpr as adjpr format 8.2, (calculated adjpr*trdvol) as trdsum format 12.2 from resdat.qttndist; quit;,CASE表达式,语句格式： CASE WHEN w

6、hen-condition THEN result-expression END,例20.7 简单形式的Case表达式。 proc sql outobs=3; select stkcd,lstknm,lstdt, case when exchflg=1 then 上交所 when exchflg=2 then 深交所 else 所有交易所 end as exchflg from resdat.lstkinfo order by lstdt; quit; 例中，将股票信息表lstkinfo交易所标识（exchflg）的编码值进行转换，同时得到新的交易所标识列及相关数值。,使用ORDER BY语句

7、排序,语句格式 ORDER BY order-by-item ;,选项说明： order-by-item 可以是如下：,例20.9 对股票上市时间列进行排序。 proc sql outobs=3; select lstknm,lstdt from resdat.lstkinfo order by lstdt; quit;,可以通过指定列在SELECT子句中的整数位置对该列排列。 例20.11 按第4列排序。 proc sql outobs=3; select stkcd,lstknm,date,clpr*mcfacpr as adjpr format 8.2 from resdat.qttnd

8、ist order by 4 desc; quit;,用WHERE语句选择观测,语句格式 WHERE sql-expression 选项说明： sql-expression （见sql-expression定义）.,例20.13 用WHERE语句选择1991年以前上市的股票。 proc sql outobs=3; select lstknm, lstdt from resdat.lstkinfo where lstdt31dec1991d; quit;,使用IN算符 例20.14 简单IN算符用法。 proc sql outobs=3; select lstknm, stkcd from re

9、sdat.lstkinfo where stkcd in (000001 600651 000004); quit;,使用BETWEEN-AND 算符,例20.16 使用BETWEEN-AND算符选择满足一定范围的观测。 proc sql; create table stkinfo1991 as select * from resdat.lstkinfo where lstdt between 1jan1991d and 31dec1991d; quit;,使用LIKE算符,例20.17 使用匹配算符LIKE选择观测。 proc sql; select stkcd,lstknm from re

10、sdat.lstkinfo where lstknm like ST%; quit;,使用汇总函数汇总数据,使用汇总函数（aggregate function or summary function）可以产生数据的统计量。,用WHERE子句汇总数据,例20.19 使用乘法函数算出代码为000002的股票调整后的收盘价。 proc sql outobs=3; select stkcd,lstknm,clpr*mcfacpr as adjpr from resdat.qttndist where stkcd=000002; quit;,使用SUM函数,例20.20 使用sum函数计算深发展历年派发

11、现金红利总额。 proc sql; select sum(dividend) format=8.2 as totledv from resdat.stk000001; quit;,观测数汇总,语句格式： Select count(distinct ) as ,proc sql; title Number of total Rows; select count(*) as number from resdat.lstkinfo; quit;,使用GROUP BY子句进行分组汇总,语句格式 ,选项说明：,例20.22 分组进行汇总。 proc sql outobs=3; title 2005年股票

12、月收益平均值; select stkcd, avg(monret) from resdat.monret where 1jan2005d=date=31dec2005d group by stkcd; quit;,用HAVING子句选择分组数据,语句格式 ,例20.25 算出A股市场股票2005年的交易天数。 proc sql; select stkcd,count(*)as trday from resdat.dret where 1jan2005d=date=31dec2005d group by stkcd having substr(stkcd,1,1) in (0,6) or sub

13、str(stkcd,1,2)=99; quit;,第21章 SQL从多个表中检索数据,清华大学经管学院 朱世武 Z Resdat样本数据： SAS论坛： ,本章内容： 使用连接从多个表中选取数据； 使用子查询通过表与表之间的联系选取数据； 使用SET算符合并查询结果。 如果没有另外说明，本章的PROC SQL语对表和视图都适用。,使用连接语句JOIN从多个表中检索数据,最基本的连接就是两个表的简单连接。 例21.1 表china与表 usa的简单连接程序： proc sql; select * from resdat.china, resdat.usa; quit;,JOIN方式分类,通常连接

14、Join方式分为两种类型： 内部join，只返回连接表中匹配连接条件的行。 外部join，是内部连接的补充，还包括除内部连接部分以外不符合连接条件的行。 外部连接分三种：left 左连接,right右连接 and full完全连接。,内部连接,数据格式 Proc sql; Select object-item from table-name alias, table-name alias Where sql-expression;,例21.2 只对相同水平的运动员进行连接。 proc sql; select * from resdat.china, resdat.usa where china

15、.level=usa.level; quit;,使用表的别名,通常的查询时会遇到两个表有相同名字的列，为了在引用时不产生混淆，需要在列名前加上表名或者表的别名。 语句格式: From table-name table-alias,proc sql; select * from resdat.china as a, resdat.usa as b where a.level=b.level; quit;,使用关键词INNER JOIN的内部连接,语句格式： From table-name JOIN table-name ON sql-expression,proc sql; select * f

16、rom resdat.china a inner join resdat.china b on a.level=b.level; order by level; quit;,关键词INNER JOIN用于连接多个表的数据。关键词inner是可选的，语句中用ON代替了原来设定匹配条件的where语句。,从多于两个表的数据集中查询数据,例21.6 简单的多表连接。 proc sql outobs=3; select a.stkcd,b.lstknm,c.clpr from resdat.sampstk a,resdat.lstkinfo b,resdat.qttndist c where a.st

17、kcd=b.stkcd and b.stkcd=c.stkcd and a.stkcd=c.stkcd; quit;,不同形式的外部连接,语句格式 From table-name LEFT JOIN | RIGHT JOIN | FULL JOIN,左外部连接,proc sql; select * from resdat.china a left join resdat.usa b on a.level=b.level; quit;,结果比以前的内部连接多了一行，该行就是Table china与Table usa不匹配的行，不匹配行中Table usa的列为缺失值。,右外部连接,proc sq

18、l; select * from resdat.china a right join resdat.usa b on a.level=b.level; quit;,结果比以前的内部连接多了一行，该行就是Table usa与Table china不匹配的行，不匹配行中Table china的列都是缺失值。,完全外部连接,proc sql; select * from resdat.china a full join resdat.usa b on a.level=b.level; quit;,结果显示，两个表中所有不匹配的行都出现在完全连接的输出结果中。,MERGE语句和JOIN连接比较,所有行

19、匹配无重复值情况,两个表中的by变量的值都相等且没有重复值的时候，可以使用一个内部连连接来产生同样的效果。 例21.7 BY变量值相等且没有重复值。 Table a Table b code manager code Assitant 145 Max 145 Tracy 150 Jack 150 Yao 155 Paul 155 Chen 程序如下： data merge1; merge a b; by code; run;,proc print data=merge1 noobs; title Table MERGE1; run;,Merge在合并前的两个数据集已经按code排过序，而PRO

20、C SQL则不需要排序，下面程序给出和上面同样的结果。 proc sql; title Table MERGE1; select a.code, a.manager, b.Assitant from a, b where a.code=b.code; quit;,有重复值情况,当用来连接两个表的列变量或者BY组中有重复值时，Merge和Proc sql的处理方式有所区别。 例21.9 BY组中有重复值。 Table newone Table newtwo code Manager code Assistant 145 Max 145 Jerry 145 Xam 145 Tracy 155 Pa

21、ul 155 Chen Data步 data merge3; merge a b; by code; run; proc print data=merge3 noobs; title Table MERGE3; run;,若用SQL，则会出现下面的结果： Proc sql; Title Table Merge3; Select a.code, a.manager, b.assistant From a full join b On a.code=b.code; quit;,使用子查询语句选择数据,语言格式： (select .from.);,产生单个值的子查询,例21.10 考虑例21.9的两

22、个表。 Proc sql; Title Which Manager has the same code as Assistant Chen; Select * From a Where code eq (select code from b where assistant=Chen); Quit；,产生多个值的子查询,例21.11 根据表sampstk中给定股票的股票代码，从表lstkinfo中选出相应的股票信息。 Proc sql; select stkcd,lstknm,lstdt from resdat.lstkinfo where stkcd in (select stkcd from resdat.sampstk);