An Issue with importing multiple excel sheet to a sas dataset - Thanks

[need_sas_help]

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.

[Aman4SAS]

Thanks, i will try to look into. Just drop few sample files and exact path, i will replicate that from myside.

Good day

[jakarman]

If you are on Windows and having acces pcfiles use the libname statement to excel.