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