topic Re: Importing multi Excel files in SAS Procedures

Importing multi Excel files

Lani — Fri, 07 May 2010 21:31:54 GMT

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;

Re: Importing multi Excel files

Ksharp — Mon, 10 May 2010 12:45:54 GMT

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

Importing multi Excel files

SAShole — Wed, 21 Mar 2012 15:48:27 GMT

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

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

Thasnk!

Importing multi Excel files

Ksharp — Thu, 22 Mar 2012 02:48:28 GMT

You can add quote around the path.

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

Ksharp

Re: Importing multi Excel files

hilary27 — Thu, 12 Jul 2012 21:58:21 GMT

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!

Re: Importing multi Excel files

Ksharp — Fri, 13 Jul 2012 03:24:08 GMT

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.

Re: Importing multi Excel files

hilary27 — Fri, 13 Jul 2012 05:09:28 GMT

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!

Re: Importing multi Excel files

SASKiwi — Fri, 13 Jul 2012 05:22:16 GMT

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;

Re: Importing multi Excel files

Ksharp — Fri, 13 Jul 2012 05:39:13 GMT

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

Re: Importing multi Excel files

hilary27 — Fri, 13 Jul 2012 14:20:54 GMT

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

Anyway, thanks for your reply!

Re: Importing multi Excel files

hilary27 — Fri, 13 Jul 2012 14:40:35 GMT

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

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

Re: Importing multi Excel files

Linlin — Fri, 13 Jul 2012 15:18:39 GMT

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;

Re: Importing multi Excel files

hilary27 — Fri, 13 Jul 2012 20:37:13 GMT

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

Re: Importing multi Excel files

buckeyefisher — Sat, 23 Aug 2014 20:24:42 GMT

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;

Re: Importing multi Excel files

jakarman — Sun, 24 Aug 2014 07:06:58 GMT

saskiwi already made the remark you will get a problem at the 10-th value.

The issue is putting a numeric will add blanks or zero-s to get postions filled. An REXX stem or SAS macro array is based on numbers not being adjusted.
Correct the adjustment to follow sas-macro conventions. I think 10.000 files as limit will do (4 positions).

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

TRIM(LEFT(PUT(_n_,4.))

Re: Importing multi Excel files

Ksharp — Sun, 24 Aug 2014 11:45:54 GMT

Jaap has already given you answer:

call symputx('file'||put(_n_,8. -L ),filenames,'G');

Re: Importing multi Excel files

JakeElwood — Thu, 07 Jan 2016 16:09:03 GMT

The original code cannot import any .xls files and gives me the following error. All filenames are .xls with only letters, digits, and underscores:

316
317 data _null_;
318 set file;
319 call symputx('file'||put(_n_,1.),filenames,'G');
320 run;

ERROR: Symbolic variable name FILE* must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX at line 319 column 6 is invalid.
filenames=M:\LS\Data\BannerMerge\FilesGrade350_201 _ERROR_=1 _N_=10
ERROR: Symbolic variable name FILE* must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX at line 319 column 6 is invalid.

Re: Importing multi Excel files

shekharkaul2007 — Mon, 29 May 2017 11:24:37 GMT

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.