How to import multiple excel files from a folder

Reply
N/A
Posts: 1

How to import multiple excel files from a folder

Hi there,

I am new to SAS and I need to import multiple (>100) excel file from a folder containing about 300 excel files. I have  a list with file names which I uploaded to SAS.

I want to import only those files that are on my list.

Can anyone give some directions?

Thanks for the help!

Super User
Super User
Posts: 7,413

Re: How to import multiple excel files from a folder

Hi,

There are numerous posts on this subject on these Community forums.  Just one quick one I tend to use:

data _null_;

     set your_dataset_with_the_filenames;

     call execute('proc import datafile="path_to_files\'||strip(filename)||'.xls" out='||strip(filename)||';

                          run;');

run;

The above will read in each Excel file into a dataset.  Note that Excel is not great as a data source, you may find differeing formats/ outcomes depending on the data.  If possible, save to CSV and create an import program.  Gives you more control over the output.  I have previously posted a VBA macro which would open each Excel file in a directory and save to CSV.

Ask a Question
Discussion stats
  • 1 reply
  • 799 views
  • 0 likes
  • 2 in conversation