How to use Proc IMPORT to read multiple .xlsx files?

[JeffNCSU]

Having a data folder with lots of xlsx files, and each xlsx file has several sheets, and one of these sheets is "data1".

How to use Proc IMPORT to read multiple .xlsx files? My following code has some problem.

My SAS:  9.3 (TS1M1), and excel 2010

%let DATAFILE= C:\Users\test\Desktop\test\*.xlsx;

Title "Import multiple .xlsx files";

PROC IMPORT OUT= test

            DATAFILE= "&DATAFILE"

            DBMS=EXCELCS REPLACE ;

     RANGE="data1$";

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

[SASKiwi]

If you are reading a single sheet then the SHEET = statement is the usual way:

Replace the range =  statement with sheet = 'data1';

[Reeza]

Questions:

Assuming you have multiple files (say X) with multiple sheets.

1. Are they all in the same folder or are some in subfolders?

2. Do all the files have the same structure?

3. Are you planning to have 1 output file or multiple output files (X*something)?

4. If multiple files how are you planning to name them?

[JeffNCSU]

(1) Some in subfolders.

(2) same structure.

(3)is it possible to have both? or have to use a separate Data procedure to combine them?

(4) Using input file to name them.

[Reeza]

It doesn't make sense to have both.  You can either append the files and add in a variable identifying the source file or you can keep them in separate files.

Are you comfortable with macro programming?

1. Need to get a list of the files you'll need to import. You can do this in SAS using a pipe and a datastep.

     24820 - Creating a Directory Listing Using SAS for Windows

2. Use SAS to import each workbook with all the sheets.

SAS-L: Importing multiple sheets of excel XLSX File

You can look up the indsname option to keep the source file name when appending multiple files.

[Linlin]

Hi

I did similar work a few days ago. below is the code I used. you need to modify the RED parts:

filename indata pipe 'dir N:\"External Investigators Projects"\Libon\"DS Calculator Pilot IDs" /b ';

/* put all the .xlsx file names in dataset file_list */
data file_list;
length fname $90 in_name out_name $32;
infile indata truncover;
input fname $ 90.;
in_name=translate(scan(fname,1,'.'),'_','-');
out_name=cats('_',in_name);
if upcase(scan(fname,-1,'.'))='XLSX';                                                                                                         
run;
data _null_;
  set file_list end=last;
  call symputx(cats('dsn',_n_),in_name);
  call symputx(cats('outdsn',_n_),out_name);
  if last then call symputx('n',_n_);
run;
%macro test;

   %do i=1 %to &n;
   PROC IMPORT OUT= work.&&outdsn&i.
            DATAFILE= "N:\External Investigators Projects\Libon\DS Calculator Pilot IDs\&&dsn&i...xlsx"
            DBMS=EXCEL REPLACE;
     RANGE="Sheet1$E17:E21";
     GETNAMES=NO;
     MIXED=NO;
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

%end;
%mend;
%test

data final;
  set _:;
run;

[terrabaroni]

Linlin

I got this error executing your code:

ERROR: XLSX file does not exist -> D:\file_paht\&&dsn&i...xlsx

i just tried to execute the code with only 1 file to test the file path and worked well.

Can you help?

Thanks

[Tom]

If the actual ampersands are appearing in your error message then you probably used single quotes (') around your filename instead of double quotes ("). Macro expressions are not evaluated inside of single quotes.