topic Re: How to use Proc IMPORT to read multiple .xlsx files? in SAS Procedures

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

JeffNCSU — Mon, 14 Jan 2013 16:59:40 GMT

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;

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

SASKiwi — Mon, 14 Jan 2013 18:56:22 GMT

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

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

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

Reeza — Mon, 14 Jan 2013 19:01:07 GMT

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?

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

JeffNCSU — Mon, 14 Jan 2013 19:24:38 GMT

(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.

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

Reeza — Mon, 14 Jan 2013 19:57:51 GMT

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.

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

Linlin — Mon, 14 Jan 2013 20:18:41 GMT

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;

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

terrabaroni — Sun, 09 Mar 2014 19:07:16 GMT

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

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

Tom — Mon, 10 Mar 2014 03:47:33 GMT

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.