- %macro import(xlsname);/*xlsname为所需导入excel的名称*/
- /*定义所需导入excel的地址*/
- %let dir=C:\Documents and Settings\Administrator\桌面\桌面\test;
- /*定义临时逻辑库test,并把excel中的所有sheet映射到逻辑库test中*/
- libname test "&dir.\&xlsname.";
- run;
- proc sql noprint;
- select count(distinct memname) into :number
- from sashelp.vmember
- where libname="TEST";/*计算excel中sheet的个数*/
- select compress(memname,"$") into :sheet1 - :sheet%left(&number)
- from sashelp.vmember
- where libname="TEST";/*将各个sheet的名字分别赋给宏变量*/
- quit;
- /*将各个sheet分别进行导入*/
- %do i=1 %to &sqlobs;
- proc import out=&&sheet&i. datafile="&dir.\&xlsname" dbms=excel replace;
- getnames=yes;
- sheet="&&sheet&i";
- mixed=yes;
- run;
- %end;
- %mend;
- %macro directory(dir=);
- %let rs=%sysfunc(filename(filref,&dir));
- %let did=%sysfunc(dopen(&filref));
- %let nobs=%sysfunc(dnum(&did));
- %do i=1 %to &nobs.;
- %let name=%qscan(%qsysfunc(dread(&did,&i)),1,.);
- %let ext=%qscan(%qsysfunc(dread(&did,&i)),-1,.);
- libname test "&dir.\&name..&ext.";
- run;
- proc sql noprint;
- select count(distinct memname) into :number
- from sashelp.vmember
- where libname="TEST";
- select compress(memname,"$") into :sheet1 - :sheet%left(&number)
- from sashelp.vmember
- where libname="TEST";
- quit;
- %do j=1 %to &sqlobs;
- proc import out=&&sheet&j. datafile="&dir.\&name..&ext." dbms=excel replace;
- getnames=yes;
- sheet="&&sheet&j";
- mixed=yes;
- run;
- %end;
- %end;
- %let rc=%sysfunc(dclose(&did));
- %mend;