Importing Files with Similar Names

[kz134]

Hi, 

I have xlsx files with similar names that I want to import. 

I have files:

201701 File.xlsx

201702 File.xlsx

201703 File.xlsx

201704 File.xlsx

201705 File.xlsx

...

I have proc import:

proc import datafile='/XXXXX/201701 File.xlsx' out=File_201701;
dbms=xlsx replace;getnames=yes;datarow=3;
run;

How I can import everything at once without manually having multiple proc import codes?

Thanks,

[RW9]

Depends what you know about the files, or if you have OS access or not.

filename tmp pipe 'dir "c:/temp/*.xlsx" /b';

data _null_;
  infile tmp;
  input;
  ds_name=cats("_",scan(_input_,1," "));
  call execute(cat('proc import datafile="c:/temp/',_input_,'" out=',ds_name,' dbms=xlsx replace; getnames=yes; datarow=3; run;'));
run;

Because your filenames start with a number you need to process it a bit to get a valid dataset name.  

I would really question why your getting data like this however, Excel is a really bad format, putting data in filenames is really bad practice.  Your just setting yourself up for a large amount of work.

[kz134]

Thanks for your help! Would it be easier if I change the file names to File_201701 from 201701 File? 

[RW9]

Well, it would be a more valid name.  However its more than that, one simple plain text file CSV would be far more usable for anything, e.g:

Date,...

201701,...

...

201702,...

...

Etc.

One file to read in, one program to read it all in in a consistent repeatable method.  One dataset to then use in your program.  File is portable across systems etc.