01-14-2013 11:59 AM
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
DBMS=EXCELCS REPLACE ;
01-14-2013 01:56 PM
If you are reading a single sheet then the SHEET = statement is the usual way:
Replace the range = statement with sheet = 'data1';
01-14-2013 02:01 PM
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?
01-14-2013 02:24 PM
(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.
01-14-2013 02:57 PM
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.
2. Use SAS to import each workbook with all the sheets.
You can look up the indsname option to keep the source file name when appending multiple files.
01-14-2013 03:18 PM
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 */
length fname $90 in_name out_name $32;
infile indata truncover;
input fname $ 90.;
set file_list end=last;
if last then call symputx('n',_n_);
%do i=1 %to &n;
PROC IMPORT OUT= work.&&outdsn&i.
DATAFILE= "N:\External Investigators Projects\Libon\DS Calculator Pilot IDs\&&dsn&i...xlsx"
03-09-2014 03:07 PM
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?
03-09-2014 11:47 PM
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.