More positional parameters found than defined

[bshiferaw27]

Hi all,

 I am trying to import multiple excel file on SAS 9.4 using this macros code but kept getting an error which mentioned "More postional parameters found than defined". I was wondering why the error is occuring and how I can fix it? 

%macro drive(dir,ext); 
   %local cnt filrf rc did memcnt name; 
   %let cnt=0;          

   %let filrf=mydir;    
   %let rc=%sysfunc(filename(filrf,&dir)); 
   %let did=%sysfunc(dopen(&filrf));
    %if &did ne 0 %then %do;   
   %let memcnt=%sysfunc(dnum(&did));    

    %do i=1 %to &memcnt;              
                       
      %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);                    
                    
      %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
       %if %superq(ext) = %superq(name) %then %do;                         
          %let cnt=%eval(&cnt+1);       
          %put %qsysfunc(dread(&did,&i));  
          proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt 
             dbms=xlsx replace;            
          run;          
       %end; 
      %end;  

    %end;
      %end;
  %else %put &dir cannot be opened.;
  %let rc=%sysfunc(dclose(&did));      
             
 %mend drive;
 
%drive(c:\temp,xlsx)

Thank you in advance!

[Reeza]

Show your log and how you called it. I'm assuming you didn't use c:\temp.

[bshiferaw27]

Hi,

I used the pathway to my excel file folder instead of (c:\temp)

[PaigeMiller]

---

@bshiferaw27 wrote:

Hi,

I used the pathway to my excel file folder instead of (c:\temp)

---

Be specific, give us the exact path and file name. We can't help you without specifics.

Also, as requested above, SHOW US the entire log for this macro (not just the ERROR messages).

[Tom]

If your DIR value includes commas then you need to macro quote it.

%drive(dir=%str(c:\temp\strange,name),ext=xlsx)

Or just try adding real quotes. I suspect that the FILENAME() function call will still work with the extra quotes, but not sure about the rest of the macro.

%drive(dir="c:\temp\strange,name",ext=xlsx)

[ballardw]

The single most likely cause of that particular error is more commas used in the call to a macro then when you define. It could be a simple typo such as

%drive(c:\temp,,xlsx)   <=see the 2 commas

or using a parameter that contains one or more commas in the value, quite often in a macro variable you create.

Such as

%let path= C:\folder\bad,foldernametouse;

                                      ^  see the comma?

%drive (&path, xlsx)

So the resolved value of the Path variable has a comma and results in 3 macro parameters:

C:\folder\bad

foldernametouse

xlsx