批量导入excle

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

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

立即领取

感谢您参与论坛问题回答

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

+2 论坛币

11:12

%let dir=C:\demo;

%macro ReadXls(name);

libname excellib excel "&dir.\&name";

proc sql noprint;

create table sheetname as

select tranwrd(memname, "''", "'" ) assheetname

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 (test.xls)

请问上面这个批量导入excle宏每一句该怎么解释啊，有点看不懂，刚开始学宏程序，各路大神来解答。跪谢

扫码加我 拉你入群

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

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

分享0 收藏0 回帖

关键词：EXCLE excl 批量导入 CLE Distinct

%let dir=D:;
%let name=test.xlsx;
%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";
/*从sashelp.vstabvw表中取得sheetname,顺便把双引号变成单引号，意义不明*/
select count(DISTINCT sheetname) into :number
from sheetname;
%put &number;
quit;
/*看看有几张sheet,赋值给number*/
select DISTINCT sheetname into :sheet1 -:sheet%left(&number) from sheetname;
/*%put &sheet1;*/
/*把表名赋值给sheet1-...-sheetn;请不要把%和left分开 */
quit;

libname excellib clear;
/*取消libname*/
/*%do i=1 %to &cnt_sht;*/
%do i=1 %to &number;
/*&cnt_sht应该指&number吧*/
proc import datafile= "&dir.\&name"
out=sheet&i replace;
sheet= "&&sheet&i";
getnames=yes;
mixed=yes;
run;
/*读取sheet数据*/
proc append base=master data=sheet&i force;
run;
/*拼接sheet1-..-sheetn*/
%end ;

%mend ReadXls;

%ReadXls (test.xlsx)