学步园

最近在网上看到一些关于批量读取文件的帖子，想到今后定会遇到，特作记录。

一.Filename

1.filename function语法

a.FILENAME(fileref <,file-name> <,device-type> <,’host-options’> <,dir-ref>)

帮助文档里面有着经典的例子：

**Example 1: Assigning a Fileref to an External File ;
%let filrf=myfile;
%let rc=%sysfunc(filename(filrf, physical-filename));
%if &rc ne 0 %then
%put %sysfunc(sysmsg());
%let rc=%sysfunc(filename(filrf));

**Example 2: Assigning a System-Generated Fileref ;
%let rc=%sysfunc(filename(fname, physical-filename));
%if &rc %then
%put %sysfunc(sysmsg());
%else
%do;
*more macro statements;
%end;

**Example 3: Assigning a Fileref to a Pipe File. This example assigns the fileref MYPIPE to a pipe file with the output from the UNIX command LS, which lists the files in the directory /u/myid. Note that in a macro statement you do not enclose character strings in quotation marks.;
%let filrf=mypipe;
%let rc=%sysfunc(filename(filrf, %str(ls /u/myid), pipe));

2.filename statement语法

a.FILENAME fileref<device-type>'external-file' <options><operating-environment-options>;

b.FILENAME fileref<device-type><options><operating-environment-options>;

c.FILENAME fileref CLEAR | _ALL_ CLEAR;

d.FILENAME fileref LIST | _ALL_ LIST;

Yves DeGuire的一篇PPT中例举了几个例子来说明filename statement的几种功能：

**To access an external file on disk;
filename copyin "&SASROOT\SAS 9.1\core\sample\contents.sas";
filename copyout disk"C:\temp\contents.sas";
data _null_;
   infile copyin sharebuffers;
   file copyout;
   input;put _infile_;
run;

**To create a temporary file that exists only as long as the filename is assigned.;
filename copyin"&SASROOT\SAS 9.1\core\sample\contents.sas";
filename copyout temp;
data _null_;
   infile copyin sharebuffers;
   file copyout;
   input;
   put _infile_;
run;

除了temp外还有一些options，例如dummy可以提供一个空的文件或将结果输出到文件，clipbrd 可以从主机上的剪贴板读取数据或将数据写入剪贴板,pipe则可以将数据写入pipe或从中读取数据，catalog可以将SAS的目录定位到外部文件，email可以利用sas发送邮件，ftp和url则可以利用FTP和URL访问远程文件。

二 Pipe

首先pipe是什么，它是SAS中两中处理间的信息传输管道，其中存在两种Pipe，即：Unnamed pipe和Named pipe。后者主要处理SAS和其他软件间的信息交流，前者则可以用来调用外部代码或在不创建中间文件情况下更改输入、输出、报错等信息。这是利用主程序和子程序间的关系，其中filename 语句下面的是子程序。Unnamed Pipe 与filename一起使用可以用来解决涉及外部文件的很多问题，姑且算作Filename语句的一种延伸吧，语法如下：

filename <fileref> pipe “<command>”;

其中command是DOS命令，pipe将command里面的信息导入虚拟文件fileref里面，再在data步里面调用。例如我们需要读取电脑里面一个文件夹(D:\TEMP)下面所有的txt文件：

filename temp pipe "D:\TEMP\*.txt /b";

可以进一步将文件名导入宏变量进行进一步操作

data _null_;
 infile terms pad missover end=eof;
 input @1 filename $12.;
 call symput('file' || left(_n_),filename);
 if eof then call symput('nfiles',left(_n_));
run;

三 应用

国内很多sas论坛(如SASOR,MYSAS,人大经济论坛)都出现过批量导入的问题，以下几个帖子算是比较具有代表性的。

http://sasor.feoh.net/viewtopic.php?t=2916

http://sasor.feoh.net/viewtopic.php?t=4555

http://mysas.net/forum/viewtopic.php?f=4&t=4832&p=16201&hilit=%E6%89%B9%E9%87%8F+%E6%89%B9%E9%87%8F%E5%AF%BC%E5%85%A5#p16201

http://bbs.pinggu.org/thread-963079-2-1.html

http://bbs.pinggu.org/thread-424967-1-1.html

很多博客上面也都就类似问题进行了剖析，这方面的文章也不在少数，下面摘录一些个人认为具有代表性的案例：

1.读取文件夹下多个excel表格

options pageno=1 ;
options mprint ;
%let my_dir = d:\temp\ ;
filename xcl_fil pipe "dir &my_dir.*.xls /b";
%MACRO Mult_Fil(PIPEin=,DSout=);
 %LOCAL i ;
    data _NULL_ ;
       infile &PIPEin end=last;
       retain k 0 ;
       k + 1 ;
       length f_name1 f_name2 $ 60 ;
       input f_name1 $ ;
       f_name2 = "&my_dir"||trim(left(f_name1));
       ck = compress(put(k,3.));
        * This will produce both the filename ;
        * and the path/filename ;
       call symput('F'||ck,trim(left(f_name1)));
       call symput('G'||ck,f_name2);
        * Now, a count of the number of files ;
       if last then call symput('TOT_FILZ',ck);
    run;
      * Create Dataset with Dummy Record ;
    data &dsout ;
    run;
 %DO i = 1 %TO &TOT_FILZ;

    proc import datafile="&&G&i" out=__test
      dbms=excel2000 replace;
      getnames=yes ;
    data __test ;
       set __test;
        * Save the name of the file this ;
        * data came from ;
       file = "&&F&i";
    data &dsout ;
         * Stack the files ;
       set &dsout __test ;
 %END ;
    data &dsout ;
       set &dsout;
        * remove Dummy Record ;
       if _N_ = 1 then delete;
    run;
%MEND Mult_Fil ;
%mult_fil(PIPEin=xcl_fil,DSout=perm)
proc print data=&syslast;
title "Reading All Files";
run;

2.读取多个文本文件

/*Program by Xiamen University Zhichao.luo@gmail.com, Any comments are welcome*/

filename folder pipe 'dir C:\temp /b'; /*指定待获取文件名的文件夹*/
data file_list; 
length fname $13.; /*设定文件名的长度及变量名*/
infile folder truncover; /* 获取文件名 */
input fname $13.; /* 将文件名写入fname变量中*/
call symput ('num_files',_n_);
/* 将文件数量保存到宏变量num_files中*/
run;

/*创建宏列别*/
data importcsv;
set file_list;
filename= trim(fname);
a='%importcsv(';
b=',';
c=')';
file "d:\temp\port.txt";
put a $ filename $c;
run;
/*导入数据*/

%macro importcsv(filename);
data &filename;
/*Rember to use double */
infile "c:\temp\&filename" delimiter =',' MISSOVER DSD firstobs=2;
input var1 var2 var3;
run;
%mend;
%include "D:\temp\port.txt";

对于变量不同的文件的读入问题，《A Macro for Reading Multiple Text Files》一文中有详细讨论。

参考文献：

<sas9.2 base dictionary>

<The FilenameRevisited>

<Using Unnamed Pipes to Simplify Access to External Files>

<So Many Files, So Little Time (or Inclination) to Type Their Names:Spreadsheets by the Hundreds>

更多相关文献：

《Reading a Pipe-Delimited File within a Comma-Delimited File》

《An Automated Method to Create a Descriptive Index for a Directory of SAS Programs》

《Dynamically allocating exported datasets by the combination of pipes and ‘X’ statement》

《Check out These Pipes: Using Microsoft Windows Commands from SAS®》

《Reading Compressed Text Files Using SAS® Software》

《Reading a Pipe-Delimited File within a Comma-Delimited File》

《Create Directory on Windows Without the Fleeting DOS Window》