Hi Aman, Looks like your code may work. But in my oriinal data the path has gaps as result from the last part of your code I am getting the following error:
443
444
445 %macro readfiles();
446
447 %do i= 1 %to &filecount;
448
449 %let infile= %scan(%bquote(&fname),&i,%str(|));
450
451 %let outfile=%substr(&infile,1,%eval(%index(&infile, xls)-2));
452
453 proc import datafile="I:\Anuj\temp folder\&infile." out=&outfile dbms=excel; run;
454
455 %end;
456
457 %mend readfiles;
458
459 %readfiles;
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the macro variable "OUTFILE".
1 Products Distributed for Clinical Use Ver1.0 (2008_JDE & SAP)_20140902 --------> Error
-----------
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS,
DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the macro variable "OUTFILE".
1 Products Distributed for Clinical Use Ver1.0 (2009_JDE & SAP)_20140902 ------------> Error
-----------
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS,
DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the macro variable "OUTFILE".
1 Products Distributed for Clinical Use Ver1.0 (2010_JDE & SAP)_20140903
-----------
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS,
DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the macro variable "OUTFILE".
1 Products Distributed for Clinical Use Ver1.0 (2012_JDE & SAP)_20140903
-----------
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS,
DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the macro variable "OUTFILE".
1 Products Distributed for Clinical Use Ver1.0 (2011_JDE & SAP)_20140903
-----------
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS,
DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the macro variable "OUTFILE".
1 Products Distributed for Clinical Use Ver1.0 (2013_JDE & SAP)_20140903
-----------
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS,
DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the macro variable "OUTFILE".
1 Products Distributed for Clinical Use Ver1.0 (2014_JDE & SAP)_20141007
-----------
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS,
DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_.
ERROR 76-322: Syntax error, statement will be ignored.
Do you know how to fix it?
mant thanks from the bottom of my heart.
Thanks, i will try to look into. Just drop few sample files and exact path, i will replicate that from myside.
Good day
If you are on Windows and having acces pcfiles use the libname statement to excel.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.