topic Re: Importing Files with Similar Names in SAS Programming

Importing Files with Similar Names

kz134 — Mon, 11 Jun 2018 13:44:22 GMT

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,

Re: Importing Files with Similar Names

RW9 — Mon, 11 Jun 2018 14:02:26 GMT

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.

Re: Importing Files with Similar Names

kz134 — Mon, 11 Jun 2018 14:20:54 GMT

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

Re: Importing Files with Similar Names

RW9 — Mon, 11 Jun 2018 14:52:21 GMT

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.