Import multiple excel files with same worksheets name into SAS

[ranikeka]

Hi

I have 300-400 excel files to import to SAS and create one single dataset. Each excel file has 6 data sheets. However all the sheet names and variable names in each sheet are same. Can you please help with the macro code? I am able to do single file?

[PeterClemmensen]

Do all sheets in all workbooks have similar variable names?

[ranikeka]

Yes all files have same variable names and
also similar sheet names. Only the content is different.

[Tom]

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 ;

[ranikeka]

Hi

I am newbie in SAS and trying as much. can you please help in understating this code completely.I am using sas enterprise guide connecting with server. Thanks

[jarapoch]

Tom, although it's perfectly understood, is it possible that there's a typo in the third "put"? Should be "sheet1" the base file?