BookmarkSubscribeRSS Feed
bshiferaw27
Fluorite | Level 6
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!
5 REPLIES 5
Reeza
Super User
Show your log and how you called it. I'm assuming you didn't use c:\temp.
bshiferaw27
Fluorite | Level 6

Hi,

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

 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Tom
Super User Tom
Super User

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
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 5 replies
  • 483 views
  • 1 like
  • 5 in conversation