DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 44

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

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.XLSmiley Happy 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

Super User
Posts: 10,500

Re: An Issue with importing multiple excel sheet to a sas dataset - 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?

Super User
Super User
Posts: 6,500

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

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

   ...

Super Contributor
Posts: 265

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

run ur script in batch mode.

Super Contributor
Posts: 265

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

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;

Super User
Super User
Posts: 7,401

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

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.

Contributor
Posts: 44

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

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.

Super User
Super User
Posts: 7,401

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

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;

Contributor
Posts: 44

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

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

Contributor
Posts: 44

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

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.

Contributor
Posts: 44

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

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.

Super User
Super User
Posts: 6,500

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

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;

Contributor
Posts: 44

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

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

Super User
Super User
Posts: 6,500

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

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.

Contributor
Posts: 44

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

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

Your code worked. Thanks Tom.

Ask a Question
Discussion stats
  • 17 replies
  • 703 views
  • 3 likes
  • 6 in conversation