加载中…
个人资料
supersasmacro
supersasmacro
  • 博客等级:
  • 博客积分:0
  • 博客访问:513,864
  • 关注人气:503
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
正文 字体大小:

SAS中的SQL语句完全教程之二:数据合并与建表、建视图

(2009-04-17 09:19:31)
标签:

sas

sql

教育

分类: SQL

SAS中的SQL语句完全教程之二:数据合并与建表、建视图索引等

 

本系列全部内容主要以《SQL Processing with the SAS System (Course Notes)》为主进行讲解,本书是在网上下载下来的,但忘了是在哪个网上下的,故不能提供下载链接了,需要的话可以发邮件向我索取,我定期邮给大家,最后声明一下所有资料仅用于学习,不得用于商业目的,否则后果自负。

 

转载请注明出处:http://blog.sina.com.cn/s/blog_5d3b177c0100cm1t.html

 

1 连接joins分为内连接inner joins和外连接outer joins

内连接:仅返回匹配的数据,最多可以有32个表同时进行内连接

外连接:返回所有匹配的数据和非匹配的数据,一次只能有两个表或视图进行外连接

迪卡尔积:返回表内所有可能的匹配情况。例如表A有10*20的数据,表B有30*40的数据,则两个表的迪卡尔积有(10+30)*(20+40)=40*60的数据

 

我们先建立两个数据集:

 

data march;

   input flight $3. +5 date date7. +3 depart time5. +2 orig $3.

         +3 dest $3.  +7 miles +6 boarded +6 capacity;

   format date date7. depart time5.;

   informat date date7. depart time5.;

   cards;

219     01MAR94    9:31  LGA   LON       3442       198       250

622     01MAR94   12:19  LGA   FRA       3857       207       250

132     01MAR94   15:35  LGA   YYZ        366       115       178

271     01MAR94   13:17  LGA   PAR       3635       138       250

302     01MAR94   20:22  LGA   WAS        229       105       180

114     02MAR94    7:10  LGA   LAX       2475       119       210

202     02MAR94   10:43  LGA   ORD        740       120       210

219     02MAR94    9:31  LGA   LON       3442       147       250

132     02MAR94   15:35  LGA   YYZ        366       106       178

202     03MAR94   10:43  LGA   ORD        740       118       210

219     03MAR94    9:31  LGA   LON       3442       197       250

622     03MAR94   12:19  LGA   FRA       3857       180       250

271     03MAR94   13:17  LGA   PAR       3635       147       250

202     04MAR94   10:43  LGA   ORD        740       148       210

219     04MAR94    9:31  LGA   LON       3442       232       250

622     04MAR94   12:19  LGA   FRA       3857       137       250

132     04MAR94   15:35  LGA   YYZ        366       117       178

271     04MAR94   13:17  LGA   PAR       3635       146       250

302     04MAR94   20:22  LGA   WAS        229       115       180

114     05MAR94    7:10  LGA   LAX       2475       117       210

202     05MAR94   10:43  LGA   ORD        740       104       210

219     05MAR94    9:31  LGA   LON       3442       160       250

622     05MAR94   12:19  LGA   FRA       3857       185       250

132     05MAR94   15:35  LGA   YYZ        366       157       178

271     05MAR94   13:17  LGA   PAR       3635       177       250

114     06MAR94    7:10  LGA   LAX       2475       128       210

202     06MAR94   10:43  LGA   ORD        740       115       210

219     06MAR94    9:31  LGA   LON       3442       163       250

132     06MAR94   15:35  LGA   YYZ        366       150       178

302     06MAR94   20:22  LGA   WAS        229        66       180

114     07MAR94    7:10  LGA   LAX       2475       160       210

132     07MAR94   15:35  LGA   YYZ        366       164       178

271     07MAR94   13:17  LGA   PAR       3635       155       250

302     07MAR94   20:22  LGA   WAS        229       135       180

;

run;

 

data delay;

   input flight $3. +5 date date7. +2 orig $3. +3 dest $3. +3

         delaycat $15. +2 destype $15. +8 delay;

   informat date date7.;

   format date date7.;

   cards;

114     01MAR94  LGA   LAX   1-10 Minutes     Domestic                8

202     01MAR94  LGA   ORD   No Delay         Domestic               -5

622     01MAR94  LGA   FRA   No Delay         International          -5

132     01MAR94  LGA   YYZ   11+ Minutes      International          14

302     01MAR94  LGA   WAS   No Delay         Domestic               -2

114     02MAR94  LGA   LAX   No Delay         Domestic                0

202     02MAR94  LGA   ORD   1-10 Minutes     Domestic                5

219     02MAR94  LGA   LON   11+ Minutes      International          18

622     02MAR94  LGA   FRA   No Delay         International           0

132     02MAR94  LGA   YYZ   1-10 Minutes     International           5

271     02MAR94  LGA   PAR   1-10 Minutes     International           4

302     02MAR94  LGA   WAS   No Delay         Domestic                0

114     03MAR94  LGA   LAX   No Delay         Domestic               -1

202     03MAR94  LGA   ORD   No Delay         Domestic               -1

219     03MAR94  LGA   LON   1-10 Minutes     International           4

622     03MAR94  LGA   FRA   No Delay         International          -2

132     03MAR94  LGA   YYZ   1-10 Minutes     International           6

271     03MAR94  LGA   PAR   1-10 Minutes     International           2

302     03MAR94  LGA   WAS   1-10 Minutes     Domestic                5

114     05MAR94  LGA   LAX   No Delay         Domestic               -2

202     06MAR94  LGA   ORD   No Delay         Domestic               -3

219     06MAR94  LGA   LON   11+ Minutes      International          27

132     06MAR94  LGA   YYZ   1-10 Minutes     International           7

302     06MAR94  LGA   WAS   1-10 Minutes     Domestic                1

622     07MAR94  LGA   FRA   11+ Minutes      International          21

132     07MAR94  LGA   YYZ   No Delay         International          -2

271     07MAR94  LGA   PAR   1-10 Minutes     International           4

302     07MAR94  LGA   WAS   No Delay         Domestic                0

;

run;

 

1.1 内连接

proc sql;

    create table innerjoins as

    select a.*,b.*

    from March a,Delay b

    where a.flight=b.flight and a.date=b.date;

quit;

 

1.2 外连接

1.2.1 左连接left join

proc sql;

    create table leftjoins as

    select *

    from March a

    left join Delay b

    on a.flight=b.flight and a.date=b.date;

quit;

 

1.2.2 右连接right join

proc sql;

    create table rightjoins as

    select *

    from March a

    right join Delay b

    on a.flight=b.flight and a.date=b.date;

quit;

 

1.2.3 全连接full join

proc sql;

    create table fulljoins as

    select *

    from March a

    full join Delay b

    on a.flight=b.flight and a.date=b.date;

quit;

 

1.3 迪卡尔积

proc sql;

    create table cartesian as

    select a.*,b.*

    from March a,Delay b;

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;

    select flight,date

       from March

    except

    select flight,date

       from Delay;

quit;

注意:这里重复值已经被删除。如果要得到所有的包含重复值的数据,就要用到关键词ALL:

proc sql;

    select flight,date

       from March

    except ALL

    select flight,date

       from Delay;

quit;

如果只保留两个数据集都有的字段,则用到关键词CORRESPONDING(可简写为CORR):

proc sql;

    select *

       from March

    except CORR

    select *

       from Delay;

quit;

 

3.2 INTERSECT

找出所有延迟的航班和日期:

proc sql;

    select flight,date

       from March

    INTERSECT 

    select flight,date

       from Delay;

quit;

关键词ALL和CORRESPONDING与在EXCEPT中一样,这里不再作介绍

 

3.3 UNION

选择所有的数据,这里的重复值只出现一次

proc sql;

    select flight,date

       from March

    UNION 

    select flight,date

       from Delay;

quit;

关键词ALL和CORRESPONDING与在EXCEPT中一样,这里不再作介绍

 

3.4 OUTER UNION

proc sql;

    select flight,date

       from March

    OUTER UNION 

    select flight,date

       from Delay;

quit;

这里,如果我们用到关键词CORR,就可以将相同字段的数据结合到一起:

proc sql;

    select *

       from March

    OUTER UNION  CORR

    select *

       from Delay;

quit;

 

4 建表

4.1 建空表

4.1.1 通过指定变量建空表

proc sql noprint;

   create table percent

       (varname char(30),

       Industry char(4),

       begindate num format date9.  label='the beginning date',

       enddate num format date9. label='the ending date',

       P_1 num,

       P_5 num,

       P_95 num,

       P_99 num);

quit;

 

4.1.2 拷贝数据集来建空表

proc sql noprint;

   create table Delaycopy(keep=flight date)

   like Delay;

quit;

 

4.1.3 查询语句中,用outobs选项来建空表

proc sql noprint outobs=0;

   create table flight114 as

   select *

   from Delay;

quit;

 

4.2 对数据表增加数据

主要有三种方法:

4.2.1 SET语句

proc sql noprint;

insert into flight114

set flight='302' ,

    date='07MAR94'd,

    orig='LGA',

    dest='WAS',

    delaycat='No Delay',

    destype='Domestic', 

    delay=0;

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;

   create table flight114 as

   select *

   from Delay

   where flight='114';

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;

   create table percent

       (varname char(30),

       Industry char(4),

       begindate num format date9.  label='the beginning date',

       enddate num format date9. label='the ending date',

       percent num,

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

 Query-expression ;

例:

proc sql noprint outobs=0;

   create View  delayview as

   select *

   from Delay;

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,

Set column-name=expression,…

Where expression;

注意,这里的where一定要写清楚,如果没有的话,则会更新所有的数据。

例:

proc sql noprint;

update delay

set delaycat='Delay'

where  delay=0;

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;

    create table shoesrank as

    select *,

       (case

       when sales>100000 then 'good'

       else 'bad'

       end) as rank

    from sashelp.shoes;

quit;

 

7.3 删除行

语法:

Delete from table-name

Where expression;

例:

proc sql;

Delete from shoesrank

Where Region='Africa';

quit;

 

7.4 改变列

语法:

Alter Table table-name

       Add column-definition,column-definition,…

       Drop column-name,column-name,…

       Modify column-definition,column-definition,…

 

对已存在的数据表增加一个列:例:

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  addcolumnmore char(20) label='modify add label';

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;

0

阅读 收藏 喜欢 打印举报/Report
  

新浪BLOG意见反馈留言板 欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

新浪公司 版权所有