SAS中文论坛

标题: excel中的所有sheet导入sas [打印本页]

作者: shiyiming 时间: 2012-6-13 23:25
标题: excel中的所有sheet导入sas
遇到一个问题请大家帮忙，在一个excel薄中有很多sheet,每个sheet的名字都没有规律，想把它导入sas，
[url:2skz1h5f]http://blog.sina.com.cn/s/blog_7f8b10ef01013py6.html[/url:2skz1h5f]发现了下面的一段代码，
[code:2skz1h5f]
%let dir=C:\sasCode;
%macro ReadXls (name);
libname excellib excel "&dir.\&name";
proc sql noprint;
create table sheetname as
select tranwrd(memname, "''", "'" ) as sheetname
from sashelp.vstabvw
where libname= "EXCELLIB";
select count(DISTINCT sheetname) into :number
from sheetname;
select DISTINCT sheetname into :sheet1 - :sheet%left(&number)
from sheetname;
quit;
libname excellib clear;
%do i=1 %to &cnt_sht;
proc import datafile= "&dir.\&name"
out=sheet&i replace;
sheet= "&&sheet&i";
getnames=yes;
mixed=yes;
run;
proc append base=master data=sheet&i force;
run;
%end ;
%mend ReadXls;
%ReadXls (abc.xls)

[/code:2skz1h5f]

试了上述代码后，产生如下错误

NOTE: Libref EXCELLIB was successfully assigned as follows:
   Engine:       EXCEL
   Physical Name: C:\sasCode\abc.xls
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: Table WORK.SHEETNAME created, with 0 rows and 1 columns.

WARNING: INTO Clause :sheet1 thru :sheet0 does not specify a valid sequence of macro variables.
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
   real time          0.29 seconds
   cpu time          0.03 seconds
NOTE: Libref EXCELLIB has been deassigned.
WARNING: Apparent symbolic reference CNT_SHT not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition
   was: &cnt_sht
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro READXLS will stop executing.

阅读代码后，感觉是下面一段有问题
[code:2skz1h5f]

libname excellib excel "&dir.\&name";
proc sql noprint;
create table sheetname as
select tranwrd(memname, "''", "'" ) as sheetname
from sashelp.vstabvw
where libname= "EXCELLIB";

[/code:2skz1h5f]

其实，我对
from sashelp.vstabvw
where libname= "EXCELLIB";
不了解，感觉很可能是不对的，所以向大家请教，恳请您们帮忙。
如果有别的导入方式，请告诉我一下，非常感谢。

作者: shiyiming 时间: 2012-6-14 01:21
标题: Re: excel中的所有sheet导入sas
首先，你的文件中可能没有合法的表名，造成啥名字也没有选出来。
另外，好像你把宏变量名弄错了。select count(DISTINCT sheetname) into :number，后面用的是  &cnt_sht, but &number;
再：EXCLE每个表会产生两名子，其中一个名字以$结尾，另一个没$.

试一下这段代码：
%macro test;
proc sql noprint;
select  memname  into: names separated by ' '
from sashelp.vstable
where libname= "EXCELLIT" and memname not like '%$';
quit;
%let cnt_sht=&sqlobs;

data master;
set
%do i=1 %to &cnt_sht;
%let memn=%scan(&names,&i);
excellit.&memn
%end;
;
run;
%mend;

%test;

作者: shiyiming 时间: 2012-6-15 00:22
标题: Re: excel中的所有sheet导入sas
是下面的代码吗，试了一下仍然报错，
请您再帮助看一下好吗
[code:g6lym12m]

%let dir=C:\data;
%macro test(name);
libname excellib excel "&dir.\&name";
proc sql noprint;
select memname into: names separated by ' '
from sashelp.vstable
where libname= "EXCELLIT" and memname not like '%$';
quit;
%let cnt_sht=&sqlobs;

data master;
set
%do i=1 %to &cnt_sht;
%let memn=%scan(&names,&i);
excellit.&memn
%end;
;
run;
%mend;

%test(a.xls);

[/code:g6lym12m]

作者: shiyiming 时间: 2012-6-18 22:26
标题: Re: excel中的所有sheet导入sas
一个typo 'EXCLELIT';
改:

%let dir=C:\data;
%macro test(name);
libname excellib excel "&dir.\&name";
proc sql noprint;
select memname into: names separated by ' '
from sashelp.vstable
where libname= "EXCELLIB" and memname like '%$';
quit;
%let cnt_sht=&sqlobs;

data master;
set
%do i=1 %to &cnt_sht;
%let memn=%scan(&names,&i,' ');
excelliB."&memn"n
%end;
;
run;
%mend;

%test(a.xls);

欢迎光临 SAS中文论坛 (http://mysas.net/forum/)