what do you do if you your path has spaces in it?
for example: filename lib pipe "dir D:\Down Load\*.xls /b";
Thasnk!
You can add quote around the path.
filename lib pipe 'dir "D:\Down Load\*.xls" /b'; data _null_; infile lib; input;list; run;
Ksharp
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!
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.
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!
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;
But when I replace "1." with "4.", it turns out that none of files could be read.
Anyway, thanks for your reply!
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
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
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;
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
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.
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: