BookmarkSubscribeRSS Feed
JeffNCSU
Calcite | Level 5

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;

7 REPLIES 7
SASKiwi
PROC Star

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

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

Reeza
Super User

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
Calcite | Level 5

(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
Super User

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 14459 views
  • 2 likes
  • 6 in conversation