Do all sheets in all workbooks have similar variable names?
Warning: Using Excel files for data might lead to incompatible data types between files. You will definitely see changes in the length of character variables based on what data appears in the individual file. You might also get changes in the types of variables especially if the files are created by humans.
So are these XLSX files? If so I would use the XLSX libname engine.
So step one is get the list of XLSX files. For example if your SAS session can run operating system commands you can read output of your operating systems directory command.
data files;
infile 'ls /folder/*.xlsx' pipe truncover ;
input filename $256. ;
run;
Step two is to create a empty shells with the expected structure for the 6 sheets.
data sheet1;
length A $20 B 8 C $25 ;
stop;
run;
Step three is use the list of files to generate code to append the data from the sheets.
filename code temp;
data _null_;
set files;
file code;
put 'libname xlsx xlsx ' filename :$quote. 'access=readonly;' ;
put 'proc append data=xlsx.sheet1 base=sheet1; run';
put 'proc append data=xlsx.sheet2 base=sheet2; run';
put 'libname xlsx clear;';
run;
%include code / source2 ;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: