How to import multiple .xls files into SAS?

Tag: sas Author: xuyong711 Date: 2013-09-23

I have a folder that contains a number of .xls files. The names of the file could be random. The exact number is unknown. How can I import these datasets to SAS by knowing only the folder's directory? I would have to iterate ... i have done this using Java ... I am curious can SAS do this?

Definitely can be done. Start with this, and then use call execute to repeatedly run an import macro on the resulting dataset. Sorry I'm not providing full code, but I don't want to paste a bunch of stuff that I wrote for my employer.
do you know the sheet name from each xls file you need? Assuming the sheet names are the same, this is pretty straightforward as @user2161151 says above.

Best Answer

Once you get a list of excel files in the folder (using techniques suggested above), you can put it into a macrovariable and loop through them in a macro assigning them one-by-one to a library:

%DO i=1 %TO %SYSFUNC(COUNTW(&list_of_files));
   LIBNAME xlibr EXCEL "&your_folder\%scan(&list_of_files,&i)";

   DATA imported_file_&i;
      SET xlibr.'Sheet1$'n;
   RUN;

%END;

If name(s) and number of sheets in each file may vary, then you'll need to add one more, nested, loop to iterate through all sheets of each file. Something like this:

%DO i=1 %TO %SYSFUNC(COUNTW(&list_of_files));
   LIBNAME xlibr EXCEL "&your_folder\%scan(&list_of_files,&i)";

   PROC SQL noprint;
     SELECT memname into :sheets separated by ' '
     FROM sashelp.vtable
     WHERE libname="XLIBR";
   QUIT;

   %DO j=1 %TO %SYSFUNC(COUNTW(&sheets));   
      DATA imported_file_&i&j;
         SET xlibr."%scan(&sheets,&j)$"n;
      RUN;
   %END;
%END;