DATA Step, Macro, Functions and more

Importing Files with Similar Names

Reply
New Contributor
Posts: 4

Importing Files with Similar Names

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,

 

Super User
Super User
Posts: 9,599

Re: Importing Files with Similar Names

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.

New Contributor
Posts: 4

Re: Importing Files with Similar Names

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

 

 

Super User
Super User
Posts: 9,599

Re: Importing Files with Similar Names

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. 

Ask a Question
Discussion stats
  • 3 replies
  • 83 views
  • 0 likes
  • 2 in conversation