How to write macro for importing multiple excel files(xlsx) in sas and append it

Question

I am having some 50 excel files (xlsx format) to be imported to sas and then need to append it for analysis. All the excel files header are same i.e., the variable names are same for all the file. I need macro for importing and appending all of them at a time rather than importing all the files one by one and then later append it. Your help is much appreciated.

The other issue with the excel file is that there is a blank column next between the variable name and data points. I have written a code remove it using data step but came we write this also in the macro while importing.

Data  XXX.yyy;
  Set XXX.yyy;
    if missing(coalesceC(of ASC Brand Cdesc1 Cust_ DGM Desc Family Grp1 High_Level_Product_Desc 
    Issf Name Prod_Desc Product__Code RVP SA_Desc Terr_ UOM Yr 
    )) and missing(coalesce(of Acc Int_Margin M_Cost Mth Net_Sales Sls__ Uts )) then delete;

run;

Answer 1

Another way to do this would be to use the Excel LIBNAME Engine. You declare a library to each of your files, and then read all the sheets in 1 Data Step.

In this example, I have 2 workbooks (Book1.xlsx and Book2.xlsx) in C:\temp. All data is in Sheet1. 3 variables -- X, Y, and Z. Modify as needed for your purpose.

data files;
format file $12.;
input file $;
datalines;
Book1.xlsx
Book2.xlsx
;
run;

%macro read_excel(dir,outdata,files);
   data _null_;
   set &files end=last;

   call execute("libname t" || strip(put(_n_,8.)) || " excel '&dir\" || strip(file) || "';");

   if last then
       call symput("n",_n_);
   run;

   data &outdata;
   set 
   %do i=1 %to &n;
       t&i.."Sheet1$"n
   %end;
   ;

   a = sum(x,y,z);
   if missing(a) then delete;
   run;

   %do i=1 %to &n;
      libname t&i clear;
   %end;
%mend;

%read_excel(c:\temp,data_from_excel,files);