topic Re: An Issue with importing multiple excel sheet to a sas dataset - Thanks in SAS Programming

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

need_sas_help — Thu, 09 Oct 2014 20:45:58 GMT

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

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

ballardw — Thu, 09 Oct 2014 22:56:29 GMT

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?

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

Tom — Thu, 09 Oct 2014 23:00:13 GMT

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');

...

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

Aman4SAS — Fri, 10 Oct 2014 11:32:32 GMT

run ur script in batch mode.

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

Aman4SAS — Fri, 10 Oct 2014 14:16:11 GMT

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;

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

RW9 — Fri, 10 Oct 2014 14:30:47 GMT

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.

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

jakarman — Fri, 10 Oct 2014 15:42:57 GMT

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

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

need_sas_help — Fri, 10 Oct 2014 17:16:08 GMT

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

-----------

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

-----------

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

-----------

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

-----------

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

-----------

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

-----------

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

-----------

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.

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

Aman4SAS — Fri, 10 Oct 2014 17:25:40 GMT

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

Good day

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

need_sas_help — Fri, 10 Oct 2014 17:50:15 GMT

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.

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

RW9 — Sat, 11 Oct 2014 10:25:06 GMT

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;

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

need_sas_help — Mon, 13 Oct 2014 17:12:58 GMT

Mostly worked... Smart..a very smart solution.. thank you so much.

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

need_sas_help — Mon, 13 Oct 2014 17:48:30 GMT

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.

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

need_sas_help — Mon, 13 Oct 2014 17:55:50 GMT

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.

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

Tom — Mon, 13 Oct 2014 19:38:41 GMT

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;

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

need_sas_help — Mon, 13 Oct 2014 20:06:45 GMT

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;

---

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

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

Tom — Mon, 13 Oct 2014 20:13:40 GMT

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.

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

need_sas_help — Mon, 13 Oct 2014 21:03:45 GMT

You are correct I am not good with missing semi-colons. Learnig...

Your code worked. Thanks Tom.