求把excel中多个sheet导入SAS，多个sheet

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

如题，一个sheet的话 RANGE="Sheet1$";就可以了，但要是有多个呢，怎么改可以一下子都导入到一个SAS数据集中。

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

关键词：sheet EXCEL exce xcel SHE excel

发帖同求。

用个macro做呗

libname xlsfile excel 'c:\normal_ranges.xls';/*excel指定引擎读取xls格式文件，xlsfile指定excel库*/

proc contents

    data=xlsfile._all_;

run;

proc print

     data=xlsfile.'normal_anges$'n;/*明确输出文件的工作表*/

run;

谁知道了也告诉我一声啊

其实以前我碰过类似的情况，一个excel里面50张表，结构一样，需要合并成一个dataset，我最后的方法是vba……

可以用宏写的

嘿嘿  刚才刚遇到这个问题，正好学习一下，哈哈

%macro xlread;
/**Assign a libname for excel sheet*/
LIBNAME XLSLIB "C:\NESUG-2011\Metabolite.xls" access=readonly;
/***PART1 OF THE PROGRAM USE PROC SQL TO READ THE SHEET NAMES IN THE EXCEL SHEET
SPECIFIED BY THE LIBNAME STATMENT ABOVE****/
/**creating macro variables for the sheet**/
proc sql noprint;
/***Get total Number of Sheets***/
select count(distinct(MEMNAME)) into: tot
from sashelp.vtable
where LIBNAME ='XLSLIB' AND INDEX(MEMNAME,'General')=0;
/**Get the sheet names without $ in to macro variables***/
select distinct(compress(MEMNAME,"',$")) into: s1 - :s%trim(%left(&tot))
from sashelp.vtable
where LIBNAME ='XLSLIB' AND INDEX (MEMNAME,'General')=0;
/**Get the sheet names with $ in to macro variables***/
select distinct(MEMNAME) into: v1 - :v%trim (%left(&tot))
from sashelp.vtable
where LIBNAME ='XLSLIB' AND INDEX (MEMNAME,'General')=0;
/**GET NAMES WITH OUT THE DELIMETERS***/
select distinct(compress(MEMNAME,"',$,-")) into: c1 - :c%trim(%left(&tot))
from sashelp.vtable
where LIBNAME ='XLSLIB' AND INDEX(MEMNAME,'General')=0;
Quit;


/***PART2 THE ABOVE MACRO VARIABLE TOT IS USE TO RUN A DO LOOP SO THAT WE HAVE TO
CREATE NAMES OF THE VARIABLES****/
%do i=1 %to &tot;
/*Create macro variables to store names of variables for reach dataset*/
proc sql noprint;
select COUNT(distinct(NAME)) into: T
from sashelp.vcolumn
where LIBNAME ='XLSLIB' AND MEMNAME="&&v&i." and SUBSTR(NAME,1,1)^='F';
select distinct(NAME) into: O1 - :O%trim(%left(&T))
from sashelp.vcolumn
where LIBNAME ='XLSLIB' AND MEMNAME="&&v&i." and SUBSTR(NAME,1,1)^='F';
QUIT;
/**set the datasets**/
data &&c&i.;
set xlslib."&&s&i.$"n;
RUN;
/**renaming the variable names to set all of them**/
PROC DATASETS LIBRARY=WORK;
MODIFY &&C&i.;
%do j=1 %to &T;
rename
&&O&j. =%sysfunc(compress("&&O&j.","',$,-,_ "));
%end;
quit;
run;
%end;
/***SAS 9.2 Code***/
data final;
set data1 - data&tot.:
run;
/***THIS MACRO WAS USED FROM SAS.COM WEBSITE
http://support.sas.com/kb/26/010.html***/
/**Code from SAS.COM***/
%macro names(prefix,maxnum);
%do i=1 %to &maxnum;
&prefix&i
%end;;
%mend names;

/***SAS 9.1.3 Code**/
data final;
set %names(Source,&tot);
run;
%mend;
%xlread;

Bridgenc 发表于 2013-5-25 23:15
%macro xlread;
/**Assign a libname for excel sheet*/
LIBNAME XLSLIB "C:\NESUG-2011\Metabolite.xls" ...

这么复杂啊？