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

SAS中的SQL语句完全教程之三:SQL过程步的其它特征

(2009-04-20 09:25:20)
标签:

sas

sql

教育

分类: SQL

SAS中的SQL语句完全教程之三:SQL过程步的其它特征

 

 

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

 

 

0

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

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

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

新浪公司 版权所有