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,
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.
Thanks for your help! Would it be easier if I change the file names to File_201701 from 201701 File?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.