4# sushe1527

能看一下问题吗?

271  %let path=C:\test\;
272  %let OutPrefix=gan;
273  x "dir &path.*.xls /b > &path.fname.txt";
274
275  %macro importxls(filename,ith);
276  proc import datafile="&path.&filename" out=&OutPrefix.&ith dbms=excel replace;

277  run;
WARNING: The quoted string currently being processed has become more than 262 characters long.
         You may have unbalanced quotation marks.
278
279  data all;
280     set %if &ith ne 1 %then all; &OutPrefix.&ith;
281  run;
282  %mend;
WARNING: Apparent invocation of macro IMPORTXLS not resolved.
283
284  data _null_;
285  infile "&path.fname.txt";
286  input filename & $100.;
287  call execute('%importxls(' || trim(filename) || ',' || _n_ || ')');
288  run;

5# MyLoveSas

我也没能成功运行这程序, 加不加空格都一样?

400  %let extfile = xls ;
401  %let dirpath= C:\test\ ;
402  filename dirpipe pipe 'dir &dirpath.\*..&extfile./b';
403
404  data _info ;
405    length filepath $120. ;
402  filename dirpipe pipe 'dir &dirpath.\*..&extfile./b';
                                                        --
                                                        49
NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS
             release.  Inserting white space between a quoted string and the succeeding
             identifier is recommended.

406    infile terms pad missover end=eof;
407    input @1 filename $20.;
408    filepath = "&dirpath" || trim(filename) ;
409    filename = scan( filename , 1 ) ;
410    call symput('filepath'
                            -
                            49
410!  || left(_n_),filepath);
NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS
             release.  Inserting white space between a quoted string and the succeeding
             identifier is recommended.

411    call symput('filename' || left(_n_),filename);
412    if eof then call symput('nfiles',left(_n_));
413  run;
414
415  %macro importdbf ;
416    %do i = 1 %to &nfiles ;
417      proc import datafile = "&&filepath&i" out = %trim(&&filename&i) dbms = &extfile ;

418      run ;
419    %end ;
420
421    proc sql noprint ;
422      select filename into : datasets separated by ' ' from _info ;
423    quit ;
424
425    data all ;
426      set &datasets ;
427    run ;
428  %mend importdbf ;
429
430  %importdbf ;

xls名字不能太长的原因

9# sushe1527


我用a1.xls, 很奇怪

filename dst pipe'dir e:\mysasfiles\excel/b';
%macro import(filename);
        proc import out=&filename
                datafile="e:\mysasfiles\excel\&filename..xls"
                dbms=excel replace;
        run;
%mend import;
data tmp;
        length fname $ 200;
        infile dst;
        input fname $ ;
        if scan(fname,-1,'.')^='xls' then delete;
        pe=prxparse('s/.xls//');
        call prxchange(pe,-1,fname);
        call execute('%import('||fname||')');
run;
这个也行

回复楼主如下问题

8#

5# MyLoveSas
我也没能成功运行这程序, 加不加空格都一样?



代码有几个问题：
1、filename dirpipe pipe 'dir &dirpath.\*..&extfile./b';语句需要修改为"dir &dirpath*.&extfile./b";，因为只有使用双引号时，sas macro facility 才做宏替换；
2、PROC IMPORT过程中DBMS选项不能直接设置为文件扩展名。另外不同类型的源数据，对应的statement（如getnames、datarow、sheet等）均不相同；

太感谢了, 谢谢帮助

哇
这是高难度啊

11# lwien007

请问, 有没有方法, 先定变量的长度和类型吗?
因为Excel中同一变量, 有的是数字, 有的是文本, 比如
变量: 语言 - 有的是代码 19, 7 等, 有的直接 用English
日期 - 10022009 或 10/02/2009

我想把他们都做为文本导入, 再处理. 谢谢

试试proc import的如下选项试试？

MIXED=YES | NO;
     converts numeric data values into character data values for a column that contains mixed data types. This option is valid only while importing data from Excel. The default is NO, which means that numeric data will be imported as missing values in a character column. If MIXED=YES, then the engine will assign a SAS character type for the column and convert all numeric data values to character data values. This option is valid only while reading (importing) data into SAS.

TEXTSIZE=1 to 32767
     specifies the field length that is allowed for importing Microsoft Excel 97, 2000, or 2002 Memo fields.

SCANTEXT=YES | NO;
     scans the length of text data for a data source column and uses the length of the longest string data that it finds as the SAS column width. However, if the maximum length that it finds is greater than what is specified in the TEXTSIZE= option, then the smaller value that is specified in TEXTSIZE= will be applied as the SAS variable width.

16# MyLoveSas


谢谢, 非常好

若用infile的方法，效率将大大提升。我用import的方法，100多个文件读了一个晚上。后改成infile的方法，3分钟内搞定。