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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.