How to read data from multiple excel files into a single dataset in SAS

[Nimish28]

Hi All ,

I trust you all are doing good.
I have a project where i have to import multiple excel files from a sas folder into a single sas dataset.
All the files have same numbers of columns and have xlsx format.
Can you guys please help me in that?

Thank you in advance

[Kurt_Bremser]

Excel files are very ill-suited for this, as the import into SAS (via LIBNAME XLSX or PROC IMPORT) will produce different column attributes (even the type, character or numeric!) depending on the contents.

If you have clean, constant content (fixed length, no completely missing columns), something like this might work:

%macro imp_excel(filename);
proc import
  datafile="&filename"
  out=temp
  dbsm=xlsx
  replace
;
run;

proc append
  data=temp
  base=want
  force
;
run;
%mend;

data _null_;
length fref $8 fname $200;
rc = filename(fref,"path_to_your_excel_files");
did = dopen(fref);
if did ne 0
then do;
  do i = 1 to dnum(did);
    fname = catx("/","path_to_your_excel_files",dread(did,i));
    if lowcase(scan(fname,-1,'.')) = 'xlsx' then call execute(cats('%imp_excel(',fname,')'));
  end;
  rc = dclose(did);
end;
rc = filename(fref);
run;

[TonyLMayo]

LIBNAME myEXCEL2 XLSX 'C:\somewhere\MySpreadsheetA.XLSX;
LIBNAME myEXCEL2 XLSX 'D:\somewhere\MySpreadsheed2.XLSX;

Data combined; set
set myExcel1.TabName1
myExcel1.TabName2
myExcel2.TabNameX
myExcel2.TabNameZ;
Run;


*https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acpcref/n0oj9f6i838mymn148890ckla700.htm