学步园

转自：http://www.nesug.org/Proceedings/nesug11/cc/cc17.pdf

该文主要应用proc sql将字表文件名赋予宏变量，然后循环读取字表中的变量名。

%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;

另外还有：

http://www2.sas.com/proceedings/sugi30/040-30.pdf