topic Re: More positional parameters found than defined in SAS Programming

More positional parameters found than defined

bshiferaw27 — Wed, 09 Jun 2021 18:24:16 GMT

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!

Re: More positional parameters found than defined

Reeza — Wed, 09 Jun 2021 18:28:53 GMT

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

Re: More positional parameters found than defined

bshiferaw27 — Wed, 09 Jun 2021 18:38:26 GMT

Hi,

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

Re: More positional parameters found than defined

Tom — Wed, 09 Jun 2021 19:11:08 GMT

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)

Re: More positional parameters found than defined

PaigeMiller — Wed, 09 Jun 2021 19:01:51 GMT

@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).

Re: More positional parameters found than defined

ballardw — Wed, 09 Jun 2021 20:21:02 GMT

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