06-11-2018 09:44 AM
I have xlsx files with similar names that I want to import.
I have files:
I have proc import:
proc import datafile='/XXXXX/201701 File.xlsx' out=File_201701;
How I can import everything at once without manually having multiple proc import codes?
06-11-2018 10:02 AM
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.
06-11-2018 10:52 AM
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:
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.