1、第第4 4章章 访问外部数据文件访问外部数据文件清华大学经管学院清华大学经管学院 朱世武朱世武ZResdat样本数据：样本数据：SAS论坛：论坛： 本章内容本章内容v通过IMPORT过程导入外部数据文件；v通过LIBNAME语句和库引擎连接外部数据文件；v通过SAS/ACCESS软件访问外部数据文件；v通过ODBC或远程软件平台访问外部数据文件；v创建SAS数据集方法总结。 注意：本章的一些例子需要较为复杂的软硬件环境，可能无法在单个PC机上实现操作。 访问两类外部文件访问两类外部文件 SAS系统访问的外部文件可以分为两类： PC格式的数据文件； 流行数据库(DBMS)文件。 PC格式的数据文
2、件流行数据库文件DBFOracleDIFDB2WK1,WK3,WK4SybaseEXCEL4, EXCEL5,EXCEL97, EXCEL2000MS Access访问外部数据文件方法访问外部数据文件方法 除了前面两章介绍的读入外部数据文件的方法外，SAS系统访问外部文件，特别是流行数据库文件的方法还有以下几种。 通过IMPORT过程； 通过LIBNAME语句和库引擎； 通过ACCESS过程； 通过ODBC或远程软件平台。通过通过IMPORT过程过程 IMPORT过程可以导入的外部数据文件：n PC格式的数据文件；n 以固定字符为字段分隔符的文本文件。 句法与选项说明句法与选项说明 PROC
3、IMPORT DATAFILE=filename“ | TABLE=tablename OUT=SAS-data-set ; 选项说明： DATAFILE=规定要读入外部文件的地址及名称TABLE=规定外部数据文件中的表名OUT=规定要输出的SAS数据集DBMS=规定外部数据文件格式的标识名REPLACE规定替换已存在文件应用举例应用举例 例4.1 导入EXELL数据表。proc import out=tb31datafile= D:ResDattable.xlsdbms=excel2000 replace;range=3#1$; /*导入表3.1 */getnames=yes;run;例4.
4、2 从文本文件的第二行导入数据。proc import out=b_share_1datafile= D:ResDatb_shares_1.txtdbms=dlm replace;getnames=no;datarow=2;run;通过通过LIBNAME语句和库引擎语句和库引擎 通过LIBNAME语句和库引擎连接的外部文件有两类： 其它SAS版本或分析软件的数据集； 流行数据库(DBMS)。语句格式： LIBNAME libref SAS/ACCESS-engine-name ; BMDPCRSPACCDB2FAMECHLIODBCOLEDBORACELOSIRISREMOTEREMOTE6R
5、EUTERSSPSSSYBASETERADATAV6V604V8XMLXPORTSAS8.2系统支持的库引擎 读入其它版本或分析软件数据集读入其它版本或分析软件数据集 可以通过菜单建立新逻辑库和利用LIBNAME语句连接其它SAS版本或分析软件的数据集。例4.5 连接SAS6版本数据集。libname datav6 V6 d:ResDat;run;例4.6 连接SPSS数据集。libname spss spss d: ResDat ;run;读入流行数据库（读入流行数据库（DBMS） 读入流行数据库时需要知道这些数据库的简单操作。同样可以通过菜单建立新逻辑库和利用LIBNAME来实现。例4.7
6、 用LIBNAME建立与ORACLE DBMS的连接。libname oralib oracle user=ZSW password=ZSW321path=ora7db dbindex=y;例4.8 取消逻辑库指定。libname oradb clear;例4.9 与DB2的连接。libname db2lib db2 authid=ZSW ssid=ZSW321;通过通过ACCESS过程过程 通过ACCESS过程可以实现对外部数据文件的透明访问和读写。SAS/ACCESS可以访问的主要外部数据文件： IMS-DL-ISQL/DSDB2ADABASRdbOracleSybaseIngresInf
7、ormixDBF/DIFExcelODBCACCESS过程访问外部数据须分两步完成： 创建访问描述器(Access Descriptor)； 创建基于外部数据文件的数据视窗(View)。ACCESS过程访问外部数据流程图过程访问外部数据流程图 其它平台数据库文件访问描述器Access数据视图SAS程序Access数据视图Access数据视图 SAS系统通过不同的方法建立不同的数据视窗，除了这里用ACCESS过程建立ACCESS视窗外，还有用数据步和SQL过程建立的视窗。它们都能透明访问不同平台上的数据对象。 创建访问描述器创建访问描述器 语句格式：PROC ACCESS DBMS=DBF|DI
8、F|WKn|XLS|; CREATE libref.member-name.ACCESS; required database-description statements; optional editing statements; RUN;创建访问描述器的SAS程序对于不同的外部文件有不同的形式。下面给出几种外部文件访问描述器的创建方法。 由由DBF文件创建访问描述器的一般格式：文件创建访问描述器的一般格式：PROC ACCESS DBMS=DBF; CREATE 逻辑库名.访问描述器名.ACCESS; PATH=DBF文件的地址和全名;Run;由由EXELL表创建访问描述器的一般格式：表创
9、建访问描述器的一般格式：PROC ACCESS DBMS=XLS; CREATE 逻辑库名.访问描述器名.ACCESS; PATH=XLS文件的地址和全名; /*规定运行时在LOG窗口显示列的信息 */RUN;由由ORACLE数据库创建访问描述器的一般格式：数据库创建访问描述器的一般格式：PROC ACCESS DBMS=Oracle; CREATE 逻辑库名.访问描述器名.ACCESS; USER=Oracle用户ID;ORAPW=Oracle服务器上的用户密码;TABLE=Oracle表名;PATH=Oracle驱动器、结点和表的别名或库名;RUN;例4.10 由XLS表创建访问描述器RE
10、SDAT.S000001.ACCESS。 proc access dbms=xls; create work.s000001.access; path=D:ResDatstk000001.xls; getnames yes; scantype=yes; list all; run;例4.11 由DBF文件创建访问描述器RESDAT.A600002.ACCESS。 proc access dbms=dbf; create resdat.stk000002.access; path=D:resdatstk000002.dbf; list all; run; 创建数据视窗创建数据视窗 通常基于一个访
11、问描述器可以创建多个数据视窗。SAS数据视窗(View)和SAS数据集(Data)都是数据文件，均可以作为数据对象被引用。虽然类型不同，但在同一逻辑库中名称不能重复。 创建基于访问描述器数据视窗的语句格式创建基于访问描述器数据视窗的语句格式PROC ACCESS DBMS=DBF|DIF|WKn|XLS| ACCDESC=libref.access-descriptor; CREATE libref.member-name.VIEW; SELECT column-list; optional editing statements; RUN;创建基于访问描述器的数据视窗的一般格式PROC ACC
12、ESS DBMS=数据库类型ACCDESC=访问描述器名; CREATE 逻辑库.视窗名.VIEW; SELECT ALL|DBMS表的列名; RUN;当然，对于不同的DBMS还有许多不同的控制语句。 同时创建访问描述器和数据视窗的语句格式同时创建访问描述器和数据视窗的语句格式PROC ACCESS DBMS=DBF|DIF|WKn|XLS|;CREATE libref.member-name.ACCESS; required database-description statements; optional editing statements; CREATE libref.member-n
13、ame.VIEW; SELECT column-list; optional editing statements; RUN;例4.12 创建DBF文件的数据视窗。proc access dbms=dbf accdesc=work.s000002;create work.s000002.view;select all;list view;run;例中，基于访问描述器RESDAT.s000002.ACCESS创建D B F 文 件 s t k 0 0 0 0 0 2 . D B F 的 数 据 视 窗WORK.s000002.VIEW.由数据视窗创建数据集由数据视窗创建数据集 实现这一目标的方法
14、很多，如数据步、ACCESS过程等。例4.15 利用数据步将视窗转换为数据集。data stk000001;set s000001;run;ACCESS过程将数据视窗转换为数据集格式：PROC ACCESS VIEWDESC=数据视窗名数据视窗名 OUT=数据集名数据集名;例例4.16 ACCESS过程将数据视窗转换为数据集。PROC ACCESS VIEWDESC=s000001 OUT=stk000001;Run;通过通过ODBC ODBC(Open Database Connectivity)技术是由微软提供的一个应用程序(API)接口标准。用于支持ODBC软件之间的数据访问。支持ODB
15、C的外部数据文件系统： MS AccessTextTeradataMS ExcelOracleINGRESDBaseSybaseSQL ServerParadoxDB2AS/400BtrieveINFORMIXRedbrick创建创建ODBC数据源数据源 v通过ODBC访问外部数据文件的前提是创建ODBC数据源。v不同的外部数据文件系统创建ODBC数据源的方式也不一样。创建创建ODBC引擎逻辑库引擎逻辑库 v这里只给出用菜单方式由ODBC数据源创建SAS逻辑库，实现对外部数据文件的访问。用SQL编程实现对外数据ODBC数据源的访问方法可以参考相应的技术资料，这里从略。v利用前面创建的ODBC数
16、据源TABLE和DISTS, 用菜单创建新逻辑库的方式创建SAS逻辑库ODBC1和ODBC2,读者自行操作。v实际应用时，通过ODBC访问后台数据库SQL-Server是非常简单和有效的方法。 第第23章章 SQL过程编过程编程程 清华大学经管学院清华大学经管学院 朱世武朱世武ZResdat样本数据：样本数据：SAS论坛：论坛： 本章主要内容：l使用PROC SQL选项建立和调试查询 l优化查询l表词典(dictionary tables)定义以及作用。l如何在PROC SQL中使用SAS宏工具l如何使用SAS输出传输系统(ODS)来对PROC SQL输出进行格式化。使用使用PROC SQL选
17、项来建立和调试查询选项来建立和调试查询 语句格式：语句格式：PROC SQL(OPTIONS)可用PROC SQL选项对查询进行控制。lINOBS=, OUTOBS=和LOOPS=选项通过限制观测和反复的数量来减少程序运行的时间。 lEXEC和VALIDATE语句快速检查查询的语法。lFEEDBACK选项展示出由SELECT *语句所选中的列。 lPROC SQL STIMER选项记录和展示查询运行的时间。proc sql feedback; select * from resdat.yrret;日志信息如下：346 proc sql feedback;347 select * from re
18、sdat.yrret;NOTE: Statement transforms to: select YRRET.Stkcd, YRRET.Lstknm, YRRET.Date, YRRET.Yrtrds, YRRET.Yrret from RESDAT.YRRET;348 quit;NOTE: “PROCEDURE SQL”所用时间（总处理时间）: 实际时间 0.00 秒 CPU 时间 0.00 秒例23.1 比较普通查询语句与包含子查询的查询语句。proc sql stimer;create table new as select a.stkcd,b.dret from resdat.lstk
19、info a,resdat.dret bwhere a.stkcd=b.stkcd and a.stktype=A;quit;94 proc sql stimer;NOTE: “SQL Statement”所用时间（总处理时间）: 实际时间 0.00 秒 CPU 时间 0.00 秒95 create table new as select a.stkcd,b.dret96 from resdat.lstkinfo a,resdat.dret b97 where a.stkcd=b.stkcd and a.stktype=A;NOTE: 表 WORK.NEW 创建完成，有 29339 行，2 列
20、。NOTE: “SQL Statement”所用时间（总处理时间）: 实际时间 0.04 秒 CPU 时间 0.04 秒98 quit;NOTE: “PROCEDURE SQL”所用时间（总处理时间）: 实际时间 0.00 秒CPU 时间 0.00 秒proc sql stimer;create table new as select stkcd,dret from resdat.dret where stkcd in (select stkcd from resdat.lstkinfo where stktype=A);quit;99 proc sql stimer;NOTE: “SQL S
21、tatement”所用时间（总处理时间）: 实际时间 0.01 秒 CPU 时间 0.01 秒100 create table new as select stkcd,dret101 from resdat.dret102 where stkcd in (select stkcd from resdat.lstkinfo103 where stktype=A);NOTE: 表 WORK.NEW 创建完成，有 29339 行，2 列。NOTE: “SQL Statement”所用时间（总处理时间）: 实际时间 0.07 秒 CPU 时间 0.07 秒104 quit;NOTE: “PROCEDU
22、RE SQL”所用时间（总处理时间）: 实际时间 0.00 秒CPU 时间 0.00 秒比较两个查询的运行时间, 第一个使用连接查询的CPU用时为0.04秒，第二个使用子查询语句的CPU用时为0.07秒。一般来说，一个连接会比同等情况的子查询运行起来要快。优化查询优化查询 优化查询的方法包括：l使用简单索引index和复杂索引composite indexesl用set算符连接查询结果时，如果事先知道查询结果中没有重复的观测，或者允许结果有重复观测，可以使用关键词All来优化查询l在创建表和视图时不用ORDER BY子句。l使用线内视图代替临时表l使用连接join代替子查询l使用where表达
23、式限制结果表的大小。用表词典来访问用表词典来访问SAS系统信息系统信息表词典（DICTIONARY tables）是特殊的只读PROC SQL表。这些表包含所有的SAS数据逻辑库、SAS数据表、系统选项以及和SAS SESSION有关的外部文件信息。用DESCREIBE语句可以查看每个表词典是如何定义的，如： proc sql; describe table dictionary.tables;类似的，可以使用DESCRIBE VIEW语句查看SASHELP视图是如何构造的。 proc sql; describe view sashelp.vtable;1165 proc sql;1166 d
24、escribe view sashelp.vtable;NOTE: SQL 视图 SASHELP.VTABLE 定义为: select * from DICTIONARY.TABLES;1167 quit;使用使用DICTIONARY.TABLES 例23.3 检索在RESDAT逻辑库下所有永久性表和视图的信息。proc sql outobs=3; title All Tables and Views in the RESDAT Library; select libname, memname, memtype, nobs from dictionary.tables where libnam
25、e=RESDAT;quit; All Tables and Views in the RESDAT Library Library Member Number of PhysicalName Member Name Type Observations-RESDAT BANKIR DATA 108RESDAT BCHMKIR DATA 80483RESDAT BDID DATA 1480 使用使用DICTIONARY.COLUMNS 例23.4 找出RESDAT逻辑库下所有含有Stkcd的列。proc sql outobs=3; title All Tables that Contain the
26、 Stkcd Column; select libname, memname,name from dictionary.columns where name=Stkcd and libname=RESDAT;quit; All Tables that Contain the Stkcd Column Library Name Member Name Column Name - RESDAT DIST Stkcd RESDAT DRET Stkcd RESDAT HALT Stkcd在在PROC SQL中使用宏工具中使用宏工具 宏工具是一个很强大的编程工具，使用宏可以避免大量重复工作，改善SQL
27、程序的性能和用途。在在PROC SQL中创建宏变量中创建宏变量 使用宏工具:macro-variable可以储存查询结果。该结果可以通过宏变量被其它PROC SQL查询和SAS过程引用。 如果一个查询结果中有多个观测,则该宏变量只包含第一个观测的值。如果这个查询结果中没有观测，则不会创建宏变量。PROC SQL的自动宏量SQLOBS包含了查询所产生的观测数量。SELECT object-item INTO macro-variable-specification FROM from-list 由查询结果的第一个观测创建宏变由查询结果的第一个观测创建宏变量量 若在INTO子句中设定一个宏变量，则
28、PROC SQL会将SELECT语句中相应列的第一个观测值赋给该宏变量。例23.5 将最新股票信息表ResDat.lstkinfo中第一个观测的stkcd和lstknm赋值给宏变量stkcd和lstknm。用%PUT语句把宏变量的内容写入SAS日志。proc sql;select stkcd,lstknminto :stkcd,:lstknmfrom resdat.lstkinfo;quit;%put &stkcd &lstknm; 139 proc sql;140 select stkcd,lstknm141 into :stkcd,:lstknm142 from resdat.lstkin
29、fo;143 quit;NOTE: “PROCEDURE SQL”所用时间（总处理时间）: 实际时间 0.00 秒 CPU 时间 0.00 秒144 %put &stkcd &lstknm;000001 S深发展A从汇总函数结果中创建宏变量从汇总函数结果中创建宏变量 宏变量的一个比较有用的特性就是可以在SAS的标题中使用.例23.6 找出某只股票历年的最高收益。proc sql outobs=3;reset noprint;select max(yrret)into :myrretfrom resdat.yrret where stkcd=000002;reset print;title 最高
30、年收益为&myrret.%;select stkcd,date,yrret from resdat.yrretwhere stkcd=000002;quit; 最高年收益为2.340115% 股票代码|Sto 年收益率|Yeack Code 日期|Date rly Return-000002 1992-12-31 1.4000000002 1993-12-31 0.0925000002 1994-12-30 -0.5404在宏变量中实现连接在宏变量中实现连接 可以将一个列的值连接到一个宏变量中。这在建立一列宏变量和常数的时候非常有用。使用SEPARATED BY 关键词设定一个字符来分隔宏变量
31、。例23.7 用inobs选项限制proc sql读入前3个观测，再将其读入到宏变量中。一般用逗号或空格来分隔宏变量的值。proc sql inobs=3;select stkcdinto :stkcds separated by ,from resdat.lstkinfo;quit;%put &stkcds;197 proc sql inobs=3;198 select stkcd199 into :stkcds separated by ,200 from resdat.lstkinfo;WARNING: Only 3 records were read from RESDAT.LSTKI
32、NFO 由于 INOBS= 选项。201 quit;NOTE: “PROCEDURE SQL”所用时间（总处理时间）: 实际时间 0.00 秒 CPU 时间 0.00 秒202 %put &stkcds;000001,000002,000004使用宏创建表使用宏创建表 例例23.8 用宏计算制定代码和年份的股票交易天数。proc sql noprint;create table trdays(stkcd char(6),trdays num);%macro trad(stkcd,year);select count(*) into :countfrom resdat.dretwhere stkcd=&stkcd and year(date)=&year;insert into trdays(stkcd,trdays) values(&stkcd,&count);%mend;%trad(000002,1999);%trad(000002,2000);%trad(000016,1999);%trad(000016,2000);quit;
