Import multiple MS Excel file having similar name in the same folder

[Tri_Luong]

Hi everyone,

I am trying to import 100 different files in the same folder with the similar names (Filename 1.xls, Filename 2.xls, …., Filename 100.xls). Would you mind instructing me the shortest SAS code for it?

Thank everyone.

[Kurt_Bremser]

Do a google search for "sas import multiple excel files". You'll find a ton of answers already given to this question here in the communities.

[Tri_Luong]

Thanks KurtBremser for your replying, I did try it but as I am quite fresh with SAS hence those answers seemed be difficult for me. Besides, most of them instructed to import files with different names (whilst mine is having similar name) hence the codes were long. That was the reason why I need the community's help to show me the shortest way for my simple request mentioned.

Appreciate your sample codes if possible.

[Kurt_Bremser]

Since you have Excel files, the solution cannot be simple, as you have to use proc import, and proc import can only import one file at a time.

Which means two stages: determine the names, and then loop over those (a process amply described in the many posts found in the google search)

If you had similar-structured text files, you could read them in one data step, using wildcards in the infile statement.

On top of that, even if the Excels in question look the same on the surface, slight differences in the data will lead to different guesses made by proc import, and therefore differences in structure, and when you want to concatenate the resulting datasets, you'll run into problems.

If you want to make it easier, get rid of the Excel format in the first place and switch to something sensible, like csv.

[Tri_Luong]

Dear KurtBremser,

Thank for prompt response with kind advice.

Let say I have 5 files with the same data structures, same sheet names (only one sheet in one workbook). They are respectively: Sales 1.xls; Sales 2.xls; Sales 3.xls; Sales 4.xls; Sales 5.xls. and they are all in folder: "D:\SAS PRACTICES".

I am trying using this codes to import:

----------------

%let i = 1;

PROC IMPORT OUT = WORK.TEST

      DATAFILE= "D:\SAS PRACTICES\SALES &i. .xls"

      DBMS=XLS REPLACE;

      SHEET="SALES";

RUN;

(Then I extend it into dataset "COSOLIDATION" with the below codes)

DATA WORK.CONSOLIDATION;

       SET WORK.COSOLIDATION WORK.TEST;

RUN;

----------------

From my limited knowledge, I am planning to use "loop" to import from i = 1 to i = 5 with "DO...END" (I have tried but unfortunately it's failed).

Could you please advise me whether I can do any method like that?

[Kurt_Bremser]

Once you have this working:

%let i=1;

proc import
  out=work.test&i
  datafile="D:\SAS PRACTICES\SALES &i. .xls"
  dbms=xls
  replace
;
sheet="SALES";
run;

it is quite easy to expand, as long as you have a straight numeric sequence in your files:

%macro read_all(max_num);

%do i = 1 %to &max_num;

proc import
  out=work.test&i
  datafile="D:\SAS PRACTICES\SALES &i. .xls"
  dbms=xls
  replace
;
sheet="SALES";
run;

%end;

data consolidation;
set
%do i = 1 %to &max_num;
  work.test&i
%end;
;
run;

%mend;
%read_all(5)

will import 5 Excels and then concatenate the imported datasets into one. But be advised that you will run into problems sooner or later because of inconsistencies with the different imports. These can only be avoided with a textual file format.

[Tri_Luong]

Dear Mr. KurtBremser,

It is so excited. It worked as my expectation. Thank you so much!

Regards,