SAS编程技术SQL从多个表中检索数据.ppt

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

2、接表中匹配连接条件的行。 外部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.level=usa.level; quit;,使

3、用表的别名,通常的查询时会遇到两个表有相同名字的列，为了在引用时不产生混淆，需要在列名前加上表名或者表的别名。 语句格式: 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 * from resdat.china a inner

4、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.stkcd=b.stkcd and b.stkcd=c

5、.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 sql; select * from resdat.c

6、hina 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连接比较,所有行匹配无重复值情况,两个表中的by变量的值都相等且没

7、有重复值的时候，可以使用一个内部连连接来产生同样的效果。 例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排过序，而PROC SQL则不需要排序，下面程序给出和上面同样的结

8、果。 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 Paul 155 Chen Data步 data me

9、rge3; 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的两个表。 Proc sql; Title Which

10、 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); quit;,混

11、合子查询,例21.12 选出表resdat.yrret中所有A股2005年的年收益率。 proc sql; select stkcd, yrret from resdat.yrret a where (select stktype from resdat.lstkinfo b where a.stkcd=b.stkcd)=A and 1jan2005d=date=31dec2005d; quit;,合并两个或多个查询结果,以下的SET算符例子都基于下面两个表。 Table A x y - 1 one 2 two 2 two Three 与JOIN的横向连接不同，SET连接是竖直的连接。,Tab

12、le B x z - 1 one 2 two 4 four,由多个查询产生非重复观测 (UNION算符),proc sql; title A UNION B; select * from A union select * from B; quit;,产生只属于第一个查询的观测(EXCEPT算符),proc sql; title A EXCEPT B; select * from A except select * from B; quit;,从多个查询中产生公共部分 (INTERSECT算符),proc sql; title A INTERSECT B; select * from A int

13、ersect select * from B;,直接连接查询结果 (OUTER UNION算符),proc sql; title A OUTER UNION B; select * from A outer union select * from B;,输出结果显示为：, 5.2 平稳时间序列建模,本节将不再仅仅以一个回归方程的扰动项序列为研究对象，而是直接讨论一个平稳时间序列的建模问题。在现实中很多问题，如利率波动、收益率变化及汇率变化等通常是一个平稳序列，或者通过差分等变换可以化成一个平稳序列。 本节中介绍的ARMA模型(autoregressive moving average mode

14、ls)可以用来研究这些经济变量的变化规律，这样的一种建模方式属于时间序列分析的研究范畴。,经济时间序列不同于横截面数据存在重复抽样的情况，它是一个随机事件的惟一记录，如中国1980年2004年的进出口总额是惟一的实际发生的历史记录。从经济的角度看，这个过程是不可重复的。横截面数据中的随机变量可以非常方便地通过其均值、方差或生成数据的概率分布加以描述，但是在时间序列中这种描述很不清楚。因此，经济时间序列需要对均值和方差给出明晰的定义。, 5.2.1 平稳时间序列的概念,如果随机过程 的均值和方差、自协方差都不取决于 t，则称ut是协方差平稳的或弱平稳的：,注意，如果一个随机过程是弱平稳的，则 u

15、t 与 ut-s 之间的协方差仅取决于s ，即仅与观测值之间的间隔长度s有关，而与时期t 无关。一般所说的“平稳性”含义就是上述的弱平稳定义。,5.2.2 ARMA模型,1. 自回归模型AR(p) p 阶自回归模型记作AR(p)，满足下面的方程： (5.2.4) 其中：参数 c 为常数；1 , 2 , p 是自回归模型系数；p为自回归模型阶数；t 是均值为0，方差为 2 的白噪声序列。,2. 移动平均模型MA(q) q 阶移动平均模型记作MA(q) ，满足下面的方程： (5.2.5) 其中：参数 为常数；参数1 , 2 , q 是 q 阶移动平均模型的系数；t 是均值为0，方差为 2的白噪声序

16、列。,3. ARMA(p,q)模型 (5.2.6) 显然此模型是模型(5.2.4)与(5.2.5)的组合形式，称为混合模型，常记作ARMA(p,q)。 当 p=0 时，ARMA(0, q) = MA(q) 当q = 0时，ARMA(p, 0) = AR(p), 5.2.3 ARMA模型的平稳性,1. AR(p)模型的平稳性条件 为了理解AR(p)、MA(q)和ARMA(p,q)模型的理论结构，简单的算子理论是必不可少的。对于AR(p)模型 (5.2.7) 设L为滞后算子，则有Lut ut-1, Lput ut-p，特别地， L0utut。则式（5.2.7）可以改写为：,(5.2.8),若设(L

17、) 1 - 1 L - 2 L2 - - p Lp ，令 (5.2.9) 则 (z) 是一个关于z的p次多项式，AR(p) 模型平稳的充要条件是(z) 的根全部落在单位圆之外。式(5.2.7)可以改写为滞后算子多项式的形式 可以证明如果AR(p)模型满足平稳性条件，则式(5.2.10)可以表示为MA()的形式，从而可以推导出来任何一个AR(p)模型均可以表示为白噪声序列的线性组合。,(5.2.10),2MA(q) 模型的可逆性 考察MA(q) 模型 若 的根全部落在单位圆之外，则式(5.2.16)的MA算子称为可逆的。尽管不可逆时也可以表征任何给定的数据，但是一些参数估计和预测算法只有在使用可

18、逆表示时才有效。,(5.2.16),3ARMA(p,q) 模型的平稳性条件 ARMA(p,q) 模型包括了一个自回归模型AR(p)和一个移动平均模型MA(q) 或者以滞后算子多项式的形式表示,(5.2.19),(5.2.20),若令 则ARMA(p,q)模型(5.2.19)平稳的充要条件是 (z) 的根全部落在单位圆之外。,(5.2.21),ARMA模型构造了一种更为复杂的白噪声序列的线性组合，近似逼近一个平稳序列。可以看出ARMA模型的平稳性完全取决于自回归模型的参数(1 , 2 , p )，而与移动平均模型参数(1 , 2 , q )无关。,ARMA(p,q)模型中AR和MA部分应使用关键

19、词ar和ma定义。在上面AR定义中，我们已见过这种方法的例子，这对MA也同样适用。,例如，估计因变量为LS的一个2阶自回归和1阶动平均过程ARMA(2,1)，应将AR(1), MA(1), AR(2) 包含在回归因子列表中： LS c ar(1) ar(2) ma(1) 如果采用公式法输入方程，要将AR项系数明确列出，形式为： LS = c(1)+ar(1)=c(2),ar(2)=c(3)。 含有MA项只能用列表法。, 5.5.3 ARMA(p,q)模型的估计,1. ARMA(p,q)模型的输入形式,例5.5 利用 AR(1) 模型描述上证指数的变化规律,本例取我国上证收盘指数（时间期间：19

20、91年1月2007年8月）的月度时间序列S作为研究对象，用AR(1)模型描述其变化规律。首先对其做变化率， srt = 100(St-St-1)/S t-1（t = 1, 2, , T） 这样便得到了变化率序列。一般来讲，股价指数序列并不是一个平稳的序列，而通过变换后的变化率数据，是一个平稳序列，可以作为我们研究、建模的对象。记上证股价指数变化率序列为sr。,建立如下模型： t = 1, 2, , T 估计输出结果显示为：,图5.2 蓝线是上证股价指数变化率序列sr，红线是AR(1)模型的拟合值,从图5.2可以看出我国上证股价指数变化率序列在1991年1994年之间变化很大，而后逐渐变小，基本

21、在3%上下波动。近年来波动平缓，并且大多在3%下面波动。拟合曲线基本代表了这一时期的均值。,对例5.5中我国上证收盘指数（时间期间：1991年1月2007年8月）的月度时间序列S的对数差分变换LS=dlog(S)，即股票收益率用ARMA(1,1)模型来估计，来说明EViews是如何估计一个ARMA(p，q)模型的。 建立方程，输入 LS c ar(1) ma(1),估计输出显示：,估计方程可写为： t = (1.87) t = (-0.43) （0.35） R2= 0.00476 D.W. = 1.98 也可写为：,2. ARMA(p,q)模型的输出形式,一个含有AR项的模型有两种残差：第一种

22、是无条件残差 ，第二种残差是估计的一期向前预测误差 。如名所示，这种残差代表预测误差。实际上，通过利用滞后残差的预测能力，改善了无条件预测和残差。 对于含有ARMA项的模型，基于残差的回归统计量，如R2和D.W.值都是以一期向前预测误差为基础计算的。含有AR项的模型独有的统计量是估计的AR系数。对于简单AR(1)模型，1是无条件残差的一阶序列相关系数。在输出表中1用AR(1)表示，MA(1) 模型的系数1用MA(1)表示。对于平稳AR(1)模型，1在-1和+1之间。一般AR(p) 模型平稳条件是：滞后算子多项式的根的倒数在单位圆内。,含有AR或MA项的模型的估计输出和OLS模型一样，只是在回归

23、输出的底部增加了一个AR，MA多项式的根的倒数（inverted AR roots 或 inverted MA roots）。我们利用滞后算子多项式写一般的ARMA模型： 如果AR模型滞后多项式有实根或一对复根的倒数在单位圆外（即绝对值大于1，或模大于1），这意味着自回归过程是发散的。如果MA模型滞后多项式的根的倒数有在单位圆外的，说明MA过程是不可逆的，应使用不同的初值重新估计模型，直到得到满足可逆性的动平均。,4. ARMA(p,q)模型的估计选择,EViews估计AR模型采用非线性回归方法，对于MA模型采取回推技术(Box and Jenkins,1976)。这种方法的优点在于：易被理解

24、，应用广泛，易被扩展为非线性定义的模型。注意：非线性最小二乘估计渐进等于极大似然估计且渐进有效。 非线性估计方法对所有系数估计都要求初值。EViews自行确定初值。有时当迭代达到最大值时，方程终止迭代，尽管还未达到收敛。从前一步初值重新开始，使方程从中止处开始而不是从开始处开始。也可以试试不同的初值来保证估计是全部而不是局部平方误差最小，可以通过提供初值加速估计过程。,为控制ARMA估计初值，在方程定义对话框单击Options。在EViews提供的选项中，ARMA Options有几项设置初值的选择。EViews缺省方法是OLS/TSLS，这种方法先进行没有ARMA项的预备估计，再从这些值开始

25、非线性估计。另一选择是使用OLS或TSLS系数的一部分作为初值。可以选择0.3、0.5、0.8或者可以将所有初值设为零。 用户确定初值选项是User Supplied。在这个选项下，EViews使用系数向量C中的值。为设置初值，双击图标，打开系数向量C窗口，进行编辑。,为适当地设置初值，需对EViews如何为ARMA设置系数多些了解。系数向量C按下列规则为变量安排系数： （1）变量系数，以输入为序； （2）定义的AR项，以输入为序； （3）SAR，MA，SMA系数（按阶数）。 这样，下面两种定义将有同样规格的系数： Y c X ma(2) ma(1) sma(4) ar(1) Y sma(4

26、) c ar(1) ma(2) X ma(1), 5.2.4 ARMA模型的识别,1利用自相关系数和偏自相关系数识别ARMA(p, q) 模型 在实际研究中，通常的做法是根据经济指标时间序列数据的样本特征，来推断经济指标的总体（真实）特征。在实际研究中，所能获得的只是经济指标时间序列ut 的数据，根据经济指标的样本特征，来推断其总体（真实）特征。下面介绍利用ut 的自相关系数 (AC) 和偏自相关系数 (PAC) 这两个统计量去识别ARMA(p, q) 模型。,通常的，AR(p) 模型的自相关系数是随着滞后阶数k的增加而呈现指数衰减或者震荡式的衰减，具体的衰减形式取决于AR(p) 模型滞后项的

27、系数。因此，可以通过自相关系数来获得一些有关AR(p) 模型的信息，如低阶AR(p) 模型系数符号的信息。如果r1 0 ，意味着序列ut 是一阶自相关。如果rk 随着滞后阶数 k 的增加而呈几何级数减小，表明序列 ut 服从低阶自回归过程。如果 rk 在小的滞后阶数下趋于零，表明序列 ut 服从低阶移动平均过程,如果这种自相关的形式可由滞后小于k阶的自相关表示，那么偏相关在k期滞后下的值趋于零。一个纯的p 阶自回归过程AR(p) 的偏相关系数在p阶截尾，而纯的动平均函数的偏相关过程渐进趋于零。因此，如果我们能求出关于 k , k的估计值，并检验其显著性水平，就能够确定时间序列ut 的自相关的阶

28、数。,其中：t是均值为0，方差为 2的白噪声序列，ut的均值为，则自协方差k,计算可得,(5.2.27),(5.2.28),2. MA模型的识别 MA(q)模型,(5.2.26),进而得到,(5.2.29),上式表明对MA(q)模型，当 k q 时，rk = 0。ut与ut+k 不相关，这种性质通常称为截尾。即 MA(q) 模型的自相关函数在 q 步以后是截尾的。,MA(q) 的偏自相关系数的具体形式随着 q 的增加变得越来越复杂，很难给出一个关于 q 的一般表达式，但是，一个MA(q) 模型对应于一个AR() 模型。因此，MA(q) 模型的偏自相关系数一定呈现出某种衰减的形式是拖尾的。故可以

29、通过识别一个序列的偏自相关系数的拖尾形式，大致确定它应该服从一个MA(q) 过程。,3. AR模型的识别 可以不加证明的给出AR(p)过程的自相关系数,(5.2.34),其中1 , 2 , , p 是AR(p) 模型的特征多项式,(5.2.35),的p个特征根，g1 , g2 , , gp为任意给定的p个常数。,由此可知，AR(p) 模型的自相关系数会由于g1 , g2 , , gp及k取值的不同，呈现出不同的衰减形式，可能是指数式的衰减，也可能是符号交替的震荡式的衰减。例如，对于AR(1) 模型，其自相关系数为 rk1k ，当1 0时，rk呈指数式的衰减；当1 0时，rk呈震荡式的衰减。 因此，可以通过自相关系数来获得一些有关AR(p) 模型的信息，如低阶AR(p) 模型系数符号的信息。但是，对于自回归过程AR(p)，自相关系数并不能帮助我们确定AR(p) 模型的阶数p。所以，可以考虑使用偏自相关系数k,k，以便更加全面的描述自相关过程AR(p)的统计特征。,这里我们通过简单的证明给出AR(p)模型的偏自相关系数。对于一个AR(p)模型，,(5.2.36),将式（5.2.36）两边同时乘以ut-k ( k = 1, 2 , , p) ，再对方程两边取期望值并除以序列ut的方差得到如下关于系数1 , 2 , , p的线性方程组：,(