BookmarkSubscribeRSS Feed
need_sas_help
Calcite | Level 5

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

17 REPLIES 17
ballardw
Super User

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?

Tom
Super User Tom
Super User

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

   ...

Aman4SAS
Obsidian | Level 7

run ur script in batch mode.

Aman4SAS
Obsidian | Level 7

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

need_sas_help
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

need_sas_help
Calcite | Level 5

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

need_sas_help
Calcite | Level 5

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.

need_sas_help
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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;

need_sas_help
Calcite | Level 5

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

Tom
Super User Tom
Super User

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.

need_sas_help
Calcite | Level 5

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

Your code worked. Thanks Tom.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 2155 views
  • 3 likes
  • 6 in conversation