SAS中的SQL语句完全教程

来自 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>

FROM from-list 

<WHERE sql-expression> 

<GROUP BY group-by-item <, … group-by-item>> 

<HAVING sql-expression> 

<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语句中、可以在不运行查询的情况下测试语句的语法、检查列名是否合法、对于不正确的查询将打印其消息。例:

   proc sql;

       validate

3        select Region, Product,Sales

       from sashelp.shoes

       where Region = ‘Africa’;

NOTE: PROC SQL 语句有有效语法。

   quit;

此外,我们还可以用noexec选项也可以用来进行语法测试。例:

   proc sql noexec;

       select Region, Product,Sales

       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;

第二部分 数据合并与建表、建视图

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;

 

第三部分 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 :指定列宽,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: 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  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时间,而不是用实际时间,因为实际时间可能会因运行机器的环境而有很大的出入。还要注意的是,对于同一个数据集,在比较效率时,一定要考虑到该数据集第一次运行后其排序等已经发生变化,会对效率也有影响。

 

内容多来自网络,如有侵权,请联系QQ:23683716,谢谢。:一起大数据 » SAS中的SQL语句完全教程

赞 (2)
分享到:更多 ()