@DCMarks25 wrote: Hello, Thank you for your response. I did find the import code to import by cell ranges. I worked nicely, but I have 45 identical workbooks in the same file folder and I need the same 12 cells from each. You are right the sheets are not set up with column headers alone, there are a few rows of information at the top of the sheet that aren't data. I haven't SASsed in a while and I am having a hard time thinking this one through. Thank you for your time, Dominique
Take the code that works to read from one workbook. Now change it to work for another. What part of the code did you have to change? Replace those part(s) with a references to macro variable(s). Now set values to the macro variable(s) and try the code again.
Once that works define a macro using those macro variable(s) as the parameters to the macro. Test it again.
Now take your list of filenames and use it to generate one call to the macro for each file.
Something like:
%macro readone(filename);
* Read range from worksheet ;
proc import dbms=xlsx file="&filename" out=from_xlsx replace;
range='A5:B16' ;
getnames=no;
run;
* Standardize dataset structure, add filename ;
data from_xlsx ;
length filename $200 A $20 B $8 ;
set from_xlsx;
filename="&filename";
rename a=id b=code ;
format _all_;
run;
* Append to aggregate dataset ;
proc append data=from_xlsx base=all_results force;
run;
%mend readone;
* Get list of files ;
%dirtree(~/mydir);
* Read all of the XLSX files ;
data _null_;
set dirtree ;
where scan(filename,-1,'.')='xlsx';
call execute(cats('%nrstr(%readone)(',catx('/',dname,filename),')'));
run;
... View more