I am trying to import multiple excel sheet to a sas dataset: and I have used this following sas code:
%macro impt(filename,i);
proc import DATAFILE="&filename" OUT=XL&i
DBMS=xlsx REPLACE;
SHEET = "Calendar Year-Month$";
run;
%mend impt;
%let path=\\globaldata\GlobalSynthes\Post Market Surveillance\008_Global Unit Shipments\;
data _null_;
command = "dir &path.*.xls /b";;
infile dummy pipe filevar=command end=eof truncover;
do i = 1 by 1 while(not eof);
input path $128.;
path=catt("&path.",path);
put 'NOTE: ' path=;
call execute(cats('%nrstr(%impt(',path,',',i,'));'));
end;
stop;
run;
data all;
set XL:;
run;
Unfortunately this code is not working can anyone be kind enough to look at the following lo and suggest me
what might go worng or if the code I am using can produce what I am looking for?
416 %macro impt(filename,i);
417 proc import DATAFILE="&filename" OUT=XL&i
418 DBMS=xlsx REPLACE;
419 SHEET = "Calendar Year-Month$";
420 run;
421 %mend impt;
422
423
424 %let path=\\globaldata\GlobalSynthes\Post Market Surveillance\008_Global Unit Shipments\;
425
426
427 data _null_;
428 command = "dir &path.*.xls /b";;
429 infile dummy pipe filevar=command end=eof truncover;
430 do i = 1 by 1 while(not eof);
431 input path $128.;
432 path=catt("&path.",path);
433 put 'NOTE: ' path=;
434 call execute(cats('%nrstr(%impt(',path,',',i,'));'));
435 end;
436 stop;
437 run;
NOTE: The infile DUMMY is:
Unnamed Pipe Access Device,
PROCESS=dir \\globaldata\GlobalSynthes\Post Market Surveillance\008_Global Unit
Shipments\*.xls /b,
RECFM=V,LRECL=256
Stderr output:
The system cannot find the file specified. -------> Please look at this issue
NOTE: 0 records were read from the infile DUMMY.
NOTE: DATA statement used (Total process time):
real time 0.23 seconds
cpu time 0.00 seconds
438
439
440 data all;
441 set XL:;
ERROR: The data set list (WORK.XL:) does not contain any members. -------> Please look at this issue
442 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.ALL may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.ALL was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
Thanks
One thing I see:
call execute(cats('%nrstr(%impt(',path,',',i,'));'));
has multiple parameters to the macro IMPT but you show one, filename in the macro definition.
My experience with Call Execute is that it is better to generate a single parameter string and then use that in the execute. I'm pretty suspicious of the %nrstr useage in your example as well.
Have you run this with options mprint and/or symbolgen?
Either the directory doesn't exist, or the DIR command does not see it because of the embedded blank space in the directory name.
You could fix this by adding in quotes that will be passed to the OS. For example here is one way
%let path=\\globaldata\GlobalSynthes\Post Market Surveillance\008_Global Unit Shipments\;
%let command=dir /b "&path.*.xls" ;
data _null_;
command = symget('command');
...
run ur script in batch mode.
try this :
%let dirname = %nrbquote("I:\Anuj\temp folder");
%let dirname2 = I:\Anuj\temp folder;
%put &dirname &dirname2;
filename DIRLIST pipe "dir /B &dirname\*.xls";
data dirlist ;
length fname $256;
infile dirlist length=reclen ;
input fname $varying256. reclen ;
run;
proc sql noprint;
select fname into: fname separated by "|" from dirlist;
%let filecount= &sqlobs;
quit;
%put &fname &filecount;
%macro readfiles();
%do i= 1 %to &filecount;
%let infile= %scan(%bquote(&fname),&i,%str(|));
%let outfile=%substr(&infile,1,%eval(%index(&infile, xls)-2));
proc import datafile="I:\Anuj\temp folder\&infile." out=&outfile dbms=excel; run;
%end;
%mend readfiles;
%readfiles;
Just to add there, you could write that as:
%let dirname = %nrbquote("I:\Anuj\temp folder");
%let dirname2 = I:\Anuj\temp folder;
%put &dirname &dirname2;
filename DIRLIST pipe "dir /B &dirname\*.xls";
data dirlist ;
length fname $256;
infile dirlist length=reclen ;
input fname $varying256. reclen ;
run;
data _null_;
set dirlist end=last;
call execute('proc import datafile="I:\Anuj\temp folder\'||strip(fname)||'" out=imp_data'||strip(put(_n_,best.))||';
run');
if last then call execute('data want; set imp_data1-imp_data'||strip(put(_n_,best.))||'; run;');
run;
However, IMO I would automate the export of data from Excel -> VBA macro to read all the Excel files and then save as CSV - can even do concatenation at that point. Then on your import program just read the one CSV file in, with full control over the import.
Hi RW,
last part of your program is giving error for Parenthesis/ semi colon issues before the run statement. I tried different way to fix it. But no luck. Would you be kind enough to look at the errors below:
578
579 data _null_;
580
581 set dirlist end=last;
582
583 call execute('proc import datafile="\\globaldata\GlobalSynthes\Post Market
583! Surveillance\008_Global Unit Shipments\'||strip(fname)||'"
583! out=imp_data'||strip(put(_n_,best.))||';
584
585 run');
586
587 if last then call execute('data want; set imp_data1-imp_data'||strip(put(_n_,best.))||';
587! run;');
588
589 run;
NOTE: There were 7 observations read from the data set WORK.DIRLIST.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: CALL EXECUTE generated line.
1 + proc import datafile="\\globaldata\GlobalSynthes\Post Market Surveillance\008_Global Unit
Shipments\Products Distributed for Clinical Use Ver1.0 (2008_JDE & SAP)_20140902.xlsx"
out=imp_data1;
1 +
run
2 + proc import datafile="\\globaldata\GlobalSynthes\Post Market Surveillance\008_Global Unit
Shipments\Products Distributed for Clinical Use Ver1.0 (2009_JDE & SAP)_20140902.xlsx"
out=imp_data2;
ERROR: Unrecognized form of the RUN statement. Use either RUN; or RUN CANCEL;.
2 +
run
3 + proc import datafile="\\globaldata\GlobalSynthes\Post Market Surveillance\008_Global Unit
Shipments\Products Distributed for Clinical Use Ver1.0 (2010_JDE & SAP)_20140903.xlsx"
out=imp_data3;
ERROR: Unrecognized form of the RUN statement. Use either RUN; or RUN CANCEL;.
3 +
run
4 + proc import datafile="\\globaldata\GlobalSynthes\Post Market Surveillance\008_Global Unit
Shipments\Products Distributed for Clinical Use Ver1.0 (2012_JDE & SAP)_20140903.xlsx"
out=imp_data4;
ERROR: Unrecognized form of the RUN statement. Use either RUN; or RUN CANCEL;.
4 +
run
5 + proc import datafile="\\globaldata\GlobalSynthes\Post Market Surveillance\008_Global Unit
Shipments\Products Distributed for Clinical Use Ver1.0 (2011_JDE & SAP)_20140903.xlsx"
out=imp_data5;
ERROR: Unrecognized form of the RUN statement. Use either RUN; or RUN CANCEL;.
5 +
run
6 + proc import datafile="\\globaldata\GlobalSynthes\Post Market Surveillance\008_Global Unit
Shipments\Products Distributed for Clinical Use Ver1.0 (2013_JDE & SAP)_20140903.xlsx"
out=imp_data6;
ERROR: Unrecognized form of the RUN statement. Use either RUN; or RUN CANCEL;.
6 +
run
7 + proc import datafile="\\globaldata\GlobalSynthes\Post Market Surveillance\008_Global Unit
Shipments\Products Distributed for Clinical Use Ver1.0 (2014_JDE & SAP)_20141007.xlsx"
out=imp_data7;
ERROR: Unrecognized form of the RUN statement. Use either RUN; or RUN CANCEL;.
7 +
run
8 + data want;
ERROR: Unrecognized form of the RUN statement. Use either RUN; or RUN CANCEL;.
NOTE: The previous statement has been deleted.
NOTE: Line generated by the CALL EXECUTE routine.
8 + set imp_data1-imp_data7; run;
--- ---------
1 73
202
WARNING 1-322: Assuming the symbol SHEET was misspelled as set.
ERROR 73-322: Expecting an =.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
ERROR: SHEET NAME value exceeds maximum length of 32 characters.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds
Thanks a lot for helping me in my bad time.
Hi,
Yes, just missing semicolon:
data _null_;
set dirlist end=last;
call execute('proc import datafile="I:\Anuj\temp folder\'||strip(fname)||'" out=imp_data'||strip(put(_n_,best.))||'; run;');
if last then call execute('data want; set imp_data1-imp_data'||strip(put(_n_,best.))||'; run;');
run;
Mostly worked... Smart..a very smart solution.. thank you so much.
Hi Rw9,
The data is comming from one particular sheet from every excel file and that is same for every excel file (SHEET = "Calender year-Month$"). How can I use a sheet statement to your code?
Thanks a a lot. so far you code was able to pull the excel sheet... geat help.
Hope to hear from you soon.
Thanks
Tom.
Hi Rw9,
The data is comming from one particular sheet from every excel file and that is same for every excel file (SHEET = "Calender year-Month$"). How can I use a sheet statement to your code?
Thanks a a lot. so far you code was able to pull the excel sheet... geat help.
Thanks
Tom.
It is easier to see how the commands are constructed if you use the CAT functions.
data _null_;
set dirlist end=last;
call execute(
catx(' '
,'proc import'
,'datafile=',quote(cats("I:\Anuj\temp folder\",fname))
,'out=',cats('imp_data',_n_)
,';'
,'SHEET=',quote("'Calender year-Month$'")
,'run;'
)
);
if last then call execute(
catx(' '
,'data want;'
,'set imp_data1','-',cats('imp_data',_n_),';'
,'run;'
)
);
run;
Hi Tom,
Your added code is somewhat working but WITH AN ERROR as below - Would you be kind enough to look at it:
NOTE: CALL EXECUTE generated line.
1 + proc import datafile= "\\globaldata\GlobalPfiz\Post Market Surveillance\008_Global Unit
Shipments\Products Distributed for Clinical Use Ver1.0 (2008_JDE & SAP)_20140902.xlsx" out=
imp_data1 ;
NOTE: The previous statement has been deleted.
NOTE: Line generated by the CALL EXECUTE routine.
1 +
SHEET= "'Calendar Year-Month$'" run;
---
22
202
ERROR 22-322: Expecting ;. -----> ERROR ERROR
ERROR 202-322: The option or parameter is not recognized and will be ignored. -----> ERROR ERROR
NOTE: WORK.IMP_DATA1 data set was successfully created.
NOTE: The data set WORK.IMP_DATA1 has 55020 observations and 34 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 22.51 seconds
cpu time 19.25 seconds
Thanks a lot for your kind help
You really have a hard time with missing semi-colons.
That is something that you should learn to debug on your own as it is the most common mistake.
The SHEET= command is missing the semi-colon, so change this line.
,'SHEET=',quote("'Calender year-Month$'"),';'
Personally I usually write the code a file using PUT and then %INCLUDE the file. It is much easier to debug and notice things like that.
You are correct I am not good with missing semi-colons. Learnig...
Your code worked. Thanks Tom.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.