topic Re: How to read data from multiple excel files into a single dataset in SAS in SAS Enterprise Guide

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

Nimish28 — Mon, 24 May 2021 08:59:03 GMT

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

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

Kurt_Bremser — Mon, 24 May 2021 10:57:01 GMT

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;

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

TonyLMayo — Mon, 24 May 2021 14:59:18 GMT

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