Importing multi Excel files

[Lani]

Hi Everyone,

Could someone please show me how to import multiple Excel files into a data set? My below code just to read in one file. Thank you for your help. It's much appreciated.

proc import datafile="C:\SAS\data\&filename."
out=temp
replace;
DBSASLABEL=NONE;
MIXED=YES;
run;


data datain;
attrib id_nbr length=$20.
name length=$50.
address length=$50.
city length=$50.
state length=$50.
zip length=$50.
;
set temp;
run;

[Ksharp]

Conditions of Code are
1.Your operation system is Windows.
2.There is only one sheet in each xls files.
3.All of your xls files is in "d:\download\"
4.You only need to modify '2' in the code. i.e. if you have eight xls files ,only change 2 into 8;

First,you need to put the following code in one Edit Windows to make up a Marco(merge).

[pre]
options mprint mlogic symbolgen;
%macro merge(n);*n is the number of your xls files;
%do i=1 %to &n;
proc import out=datafile&i datafile="&&file&i" dbms=excel2000 replace;
sheet="sheet1$";
getname=yes;
run;
%end;
data whole;
set %do j=1 %to &n;
datafile&j
%end;
;
run;
%mend merge;
[/pre]

The compile will be passed.You will find a Macro(merge) in your Work Library.

Second,You need to put the following code in another Edit Window to run.

[pre]
filename lib pipe "dir D:\download\*.xls /b";
data file;
length filenames $ 40;
infile lib truncover;
input filenames : $;
filenames="D:\download\"||filenames;
run;

data _null_;
set file;
call symputx('file'||put(_n_,1.),filenames,'G');
run;
options sasautos=work;
%merge(2) *assuming the number of xls files is 2;
proc print data=whole;
run;
[/pre]



The code is working under SAS 9.0 + Windows XP Home . Message was edited by: Ksharp

[SAShole]

what do you do if you your path has spaces in it?

for example: filename lib pipe "dir D:\Down Load\*.xls /b";

Thasnk!

[Ksharp]

You can add quote around the path.

filename lib pipe 'dir "D:\Down Load\*.xls" /b';
data _null_;
 infile lib;
 input;list;
run;

Ksharp

[hilary27]

Hi Ksharp,

I want to import and merge more than 1000 excel files in SAS, and all are in the same structure. I tried your codes above, but it could only read the first 9 files. Do you know how to modify it?

Thanks!

[Ksharp]

Is there some log information. ERROR ? WARNING ? .....

Is there some blank or special character in your PATH ?

Do these xls file locate in the same directory or different directory ?

Or you maybe have different type of the same variable when importing these files.

[hilary27]

Thanks for your reply!

There is no blank or special character in the path, and all xls files are located in the same directory for sure.

I could show you the codes;

/* I want to extract the specific rows in Sheet1 for each excel file, and transpose each before merging them together, below is the %macro merge(n) I modified based on your code */

options mprint mlogic symbolgen;

%macro merge(n);

%do i=1 %to &n;

proc import datafile="&&all_files&i" out=one&i dbms=excel replace;

sheet="Sheet1$48:54";

getnames=no;

run;

proc transpose data=one&i out=two&i;

id F1;

run;

%end;

data whole;

set %do j=1 %to &n;

two&j

%end;

;

run;

%mend merge;

/* According your codes, data 'file' is made of each excel file's name, but when I ran your code, each cell in the data 'file' is 'C:\Users\WIN7\filename.xls', so I revised the code */

FILENAME excl pipe "dir/b C:\Users\WIN7\*.xls"; run;

data all_files;

length fl_nm $200 file_name $200;

DROP fl_nm;

infile excl truncover END=last;

input fl_nm;

file_name=scan(fl_nm,1,'.');

IF last=1 THEN call SYMPUT("nfiles",put(_n_,4.));

run;

%put &nfiles;

data _null_;

infile excl;

input;

list;

run;

data null;

set all_files;

call symputx('all_files'||put(_n_,1.),file_name,'G');

run;

/* When I ran <data null> part, there is an error, but I am pretty sure that my excel filenames contain only allowed character.

ERROR: Symbolic variable name ALL_FILES* must contain only letters, digits, and underscores.

And data 'null' actually is identical with data 'all_files', I want to ask how it should look like?

Finally I ran:

options sasautos=work;

%merge(2700);

Actually I successfully create data ONE1-ONE9, TWO1-TWO9, but after the first 9 files, there is warning:

MLOGIC(MERGE):  %DO loop index variable I is now 10; loop will iterate again.

SYMBOLGEN:  && resolves to &.

SYMBOLGEN:  Macro variable I resolves to 10

WARNING: Apparent symbolic reference ALL_FILES10 not resolved.

SYMBOLGEN:  Macro variable I resolves to 10

MPRINT(MERGE):   proc import datafile="&all_files10" out=one10 dbms=excel replace;

MPRINT(MERGE):   AEXC;

MPRINT(MERGE):   sheet="Sheet1$48:54";

MPRINT(MERGE):   getnames=no;

MPRINT(MERGE):   run;

ERROR: Unable to open file C:\Users\WIN7\&all_files10.XLS. It does not  exist or it is already

       opened exclusively by another user, or you need permission to view its data.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.23 seconds

      cpu time            0.14 seconds

Could you please look at my codes? There must be some errors but I don't know where it is.

Looking forward your reply!

[SASKiwi]

Could the problem be the bold "1." below? Suggest you change it to "4." to get numbers greater than 9.

data null;

set all_files;

call symputx('all_files'||put(_n_,1.),file_name,'G');

run;

[hilary27]

But when I replace "1." with "4.", it turns out that none of files could be read.

Anyway, thanks for your reply!

[Ksharp]

I noticed a problem in your code.

data all_files;

length fl_nm $200 file_name $200;

DROP fl_nm;

infile excl truncover END=last;

input fl_nm $ ;

file_name=scan(fl_nm,1,'.');

IF last=1 THEN call SYMPUT("nfiles",put(_n_,4.));

run;

fl_nm should be character, so add $ behind it.

and why you use file_name=scan(fl_nm,1,'.'); ?

filen_name should be your full path of xls file  Like : C:\Users\WIN7\xxxxx.xls

So change it :  filename="C:\Users\WIN7\"||fl_nm;

That might be working.

Ksharp

[hilary27]

Hi Ksharp,

I revised my code as you suggested. But it doesn't work, could you help me to look at it?

I really appreciate your help.

FILENAME excl pipe "dir/b C:\Users\WIN7\*.xls"; run;

data file;

length fl_nm $200 file_name $200;

DROP fl_nm;

infile excl truncover END=last;

input fl_nm$;

file_name="C:\Users\WIN7\"||fl_nm;

IF last=1 THEN call SYMPUT("nfiles",put(_n_,4.));

run;

%put &nfiles;

data _null_;

infile excl;

input;

list;

run;

data null;

set file;

call symputx('file'||put(_n_,1.),file_name,'G');

run;

Now, data 'file' is like "C:\Users\WIN7\xxxxx.xls", but still when I ran the following codes, there is error:

75   data null;

76   set file;

77   call symputx('file'||put(_n_,1.),file_name,'G');

78   run;

ERROR: Symbolic variable name FILE* must contain only letters, digits, and underscores.

NOTE: Argument 1 to function SYMPUTX at line 77 column 6 is invalid.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 2700 observations read from the data set WORK.FILE.

WARNING: The data set WORK.NULL may be incomplete.  When this step was stopped there were 3182

         observations and 1 variables.

WARNING: Data set WORK.NULL was not replaced because this step was stopped.

NOTE: At least one W.D format was too small for the number to be printed. The decimal may be

      shifted by the "BEST" format.

What the data 'null' should be look like?

And when I ran %merge(2700), it could still read the first 9 files only.

The successfully reading's log is:

MLOGIC(MERGE):  %DO loop index variable I is now 9; loop will iterate again.

SYMBOLGEN:  && resolves to &.

SYMBOLGEN:  Macro variable I resolves to 9

SYMBOLGEN:  Macro variable FILE9 resolves to C:\Users\WIN7\12306BM_10_AM.xls

SYMBOLGEN:  Macro variable I resolves to 9

MPRINT(MERGE):   proc import datafile="C:\Users\WIN7\12306BM_10_AM.xls" out=one9 dbms=excel

replace;

MPRINT(MERGE):   AEXC;

MPRINT(MERGE):   sheet="Sheet1$48:54";

MPRINT(MERGE):   getnames=no;

MPRINT(MERGE):   run;

SYMBOLGEN:  Macro variable _IMEXSERROR_ resolves to SERROR

NOTE: WORK.ONE9 data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.29 seconds

      cpu time            0.15 seconds

SYMBOLGEN:  Macro variable I resolves to 9

SYMBOLGEN:  Macro variable I resolves to 9

MPRINT(MERGE):   proc transpose data=one9 out=two9;

MPRINT(MERGE):   id F1;

MPRINT(MERGE):   run;

NOTE: There were 7 observations read from the data set WORK.ONE9.

NOTE: The data set WORK.TWO9 has 10 observations and 9 variables.

NOTE: PROCEDURE TRANSPOSE used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

The unsuccessful reading's log is:

MLOGIC(MERGE):  %DO loop index variable I is now 10; loop will iterate again.

SYMBOLGEN:  && resolves to &.

SYMBOLGEN:  Macro variable I resolves to 10

WARNING: Apparent symbolic reference FILE10 not resolved.

SYMBOLGEN:  Macro variable I resolves to 10

MPRINT(MERGE):   proc import datafile="&file10" out=one10 dbms=excel replace;

MPRINT(MERGE):   AEXC;

MPRINT(MERGE):   sheet="Sheet1$48:54";

MPRINT(MERGE):   getnames=no;

MPRINT(MERGE):   run;

ERROR: Unable to open file C:\Users\WIN7\&file10.XLS. It does not  exist or it is already

       opened exclusively by another user, or you need permission to view its data.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.19 seconds

      cpu time            0.10 seconds

SYMBOLGEN:  Macro variable I resolves to 10

ERROR: File WORK.ONE10.DATA does not exist.

SYMBOLGEN:  Macro variable I resolves to 10

MPRINT(MERGE):   proc transpose data=one10 out=two10;

MPRINT(MERGE):   id F1;

MPRINT(MERGE):   run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.TWO10 may be incomplete.  When this step was stopped there were 0

         observations and 0 variables.

WARNING: Data set WORK.TWO10 was not replaced because this step was stopped.

NOTE: PROCEDURE TRANSPOSE used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

Thanks!

Hilary

[Linlin]

Hi Hilary,

Do you want to try the code below?

data file;
length fl_nm $200 file_name $200;
DROP fl_nm;
infile excl truncover END=last;
input fl_nm$;
file_name="C:\Users\WIN7\"||fl_nm;
IF last=1 THEN call SYMPUT("nfiles",put(_n_,z4.));
run;
%put &nfiles;
data _null_;
infile excl;
input;
list;
run;
data null;
set file;
call symputx('file'||put(_n_,z4.),file_name,'G');
run;

options mprint mlogic symbolgen;
%macro merge(n);
%do i=1 %to &n;
%let i=%sysfunc(putn(&i,z4.));
proc import datafile="&&all_files&i" out=one&i dbms=excel replace;
sheet="Sheet1$48:54";
getnames=no;
run;
proc transpose data=one&i out=two&i;
id F1;
run;
%end;
data whole;
set %do j=1 %to &n;
%let j=%sysfunc(putn(&j,z4.));
two&j
%end;
;
run;
%mend merge;

[hilary27]

Hi Linlin,

Your codes works! All files are read successfully. I really appreciate your help.

And I am curious to know whether there is a way to make the process more efficient and fast? It took 2+ hours to read through all datafile.

Thanks,

Hilary

[shekharkaul2007]

Hi,

My queries are:

1) I have 3 files as "Excel 1" "Excel 2" "Excel 3" and after the import I have all the files but by the name  "Excel" "Excel" "Excel". I cant sort it out.

2) I am new to this type of import. Can you tell me what does     'dir "E:\Analytics\Excel\*.xls" /b'    b means in this link. And how to alter this as per our requirement.

[buckeyefisher]

Ksharp.

I used your code to import multiple files(52). It works perfectly for 9 files. 10 th onwards it stop recognizing the &.

It gives error -  "WARNING: Apparent symbolic reference FILE10 not resolved. "

here is the code

options mprint mlogic symbolgen;

%macro merge(n);*n is the number of your xls files;

%do i=1 %to &n;

proc import out=datafile&i datafile="&&file&i"  dbms=csv replace;

       *sheet="sheet1$";

       getnames=yes;

     run;

%end;

data whole;

  set %do j=1 %to &n;

        datafile&j

       %end;

     ;

run;

%mend merge;

filename lib pipe 'dir "C:\Users\awate_3\Box Sync\Projects\Pharma\DAta\Cam\Binder2013New\*.csv" /b';

data _null_;

infile lib;

input;list;

run;

data file;

  length filenames $ 100;

  infile lib truncover;

  input filenames : $;

    filenames="C:\Users\awate_3\Box Sync\Projects\Pharma\DAta\Cam\Binder2013New\"||filenames;

run;

data null;

  set file;

  call symputx('file'||put(_n_,1.),filenames,'G');

run;

options sasautos=work;

%merge(52)  *assuming the number of xls files is 2;