topic Re: Import multiple excel files with same worksheets name into SAS in SAS Enterprise Guide

Import multiple excel files with same worksheets name into SAS

ranikeka — Tue, 17 Nov 2020 13:16:06 GMT

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?

Re: Import multiple excel files with same worksheets name into SAS

PeterClemmensen — Tue, 17 Nov 2020 13:18:11 GMT

Do all sheets in all workbooks have similar variable names?

Re: Import multiple excel files with same worksheets name into SAS

ranikeka — Tue, 17 Nov 2020 13:31:08 GMT

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

Re: Import multiple excel files with same worksheets name into SAS

Tom — Tue, 17 Nov 2020 13:58:30 GMT

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 ;

Re: Import multiple excel files with same worksheets name into SAS

ranikeka — Tue, 17 Nov 2020 14:34:40 GMT

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

Re: Import multiple excel files with same worksheets name into SAS

jarapoch — Wed, 18 Nov 2020 10:00:29 GMT

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