BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

Hi,

 

I have a data table "list" which contains 2 variables: path (the full path of a fileincluding ame and extension; files can be in different subdirectories) and name (the name of the files, excluding extensions).

 

Then create a macro list for each of the variables:

 

proc sql noprint;
 select path ,the_name
 into :path separated by ',',
 :the_name separated by ','
 from list;
 quit;

Then I use these 2 macro lists in a macro that will import these files:

 

%macro import_from_all_subdirectories(file_path,file_name);

 %let k=1;

    %let the_path= %scan(&file_path, &k);
	%let name= %scan(&file_name, &k);
%do %while("&the_path" NE "");

proc import 
    datafile='&the_path'
    dbms=xls
    out=&name;
    
run;



%let k = %eval(&k + 1);
    %let the_path = %scan(&the_path, &k);
    %let name = %scan(&name, &k);
%end;


%mend;

%import_from_all_subdirectories(&path,&the_name);

But I get an error message that "More positional parameters found than defined". Please help me slove this issue.

 

Thank you! 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

You've overly complicated this a bit in several places. If you're trying to import the file, why not specify the entire path than try to compile it? And when concatenating, you forgot the \ in as a spacer.

 

Anyways, here's one way that works - tested it out.

 

https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

 

%macro list_files(dir,ext);
	%local filrf rc did memcnt name i;
	%let rc=%sysfunc(filename(filrf,&dir));
	%let did=%sysfunc(dopen(&filrf));

	%if &did eq 0 %then
		%do;
			%put Directory &dir cannot be open or does not exist;

			%return;
		%end;

	%do i = 1 %to %sysfunc(dnum(&did));
		%let name=%qsysfunc(dread(&did,&i));

		%if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then
			%do;
				%put &dir\&name;
				%let file_name =  %qscan(&name,1,.);
				%put &file_name;

				data _tmp;
					length dir $512 name $100;
					dir=symget("dir");
					name=symget("name");
					path = catx('\',dir,name);
					the_name = substr(name,1,find(name,'.')-1);
				run;

				proc append base=list data=_tmp force;
				run;

				quit;

				proc sql;
					drop table _tmp;
				quit;

			%end;
		%else %if %qscan(&name,2,.) = %then
			%do;
				%list_files(&dir\&name,&ext)
			%end;
	%end;

	%let rc=%sysfunc(dclose(&did));
	%let rc=%sysfunc(filename(filrf));
%mend list_files;

%macro import_file(path, file_name, dataset_name );

	proc import 
		datafile="&path.\&file_name."
		dbms=xlsx
		out=&dataset_name replace;
	run;

%mend;

%list_files(c:\_localData\temp, xlsx);

data _null_;
	set list;
	string = catt('%import_file(', dir, ', ',  name,', ', catt('test', put(_n_, z2.)), ');');
	call execute (string);
run;

View solution in original post

25 REPLIES 25
Reeza
Super User

Are all your files the same structure?

If so, you don't need a macro.

 

Check your 'order of operations' inside your macro. 

 

You're trying to use macro variables that don't exist when calling the macro and they'll only exist within the macro. 

 

ilikesas
Barite | Level 11

Hi Reeza,

 

I use macro variables when I call the macro because these are the macro lists that I created (I assume I can use macro lists)?

 

 

As for the structure of the files, what do you mean by that? They are all Excel files, but from different folders.

 

 

Thanks! 

ballardw
Super User

File structure, a concept completely foreign to Excel, is that the variables in each column represent the same basic data a the same column in another file. Example: the first column in two different files is an ID value that is numeric, the second is the first name of a client and the third colum represents a last name. In the same order with similar characteristics, which for character values means that the maximum lengths of any value are at least similar, say 20 characters in one file and 24 in the other. Also the actual data starts on the same row in both files and if one has column headers the other should as well. The number of rows may differ as the main difference in file layout.

 

 

 

 

ilikesas
Barite | Level 11

In this sense my files have the same structure; they all have 2 varibales with the same type of data.

ballardw
Super User

This call using your macro variables created a shown will fail in almost every case:

%import_from_all_subdirectories(&path,&the_name);

Reason: You define the macro with two parameters.

 

The values of your macro variables will have commas embedded because you use "separated by ',' "

So the second value in the &path list will be treated by the macro processor as the second parameter and everything else after will be unrecognized parameters and generate the error: More positional parameters found then defined.

Please see this example:

%let parm = a, b, c;

%macro dummy (var);
%mend;

%dummy(&parm);

So you probably should use " separated by ' '  " in building the parameter list in Proc Sql.

 

Note that passing commas in macro variables is a common problem causer.

ilikesas
Barite | Level 11

I did the appropriate change, but now I get another error:

 

Physical file does not exist. C:\Documents and Setting\HP_Administrator\&the_path.xls

 

So for some reason it put the &the_path as the name of the file! 

Reeza
Super User

The macro variable 'swallows' the period so you need 2 periods, one to denote the end of the macro variable and one for the extension.

 

C:\Documents and Setting\HP_Administrator\&the_path..xls
ilikesas
Barite | Level 11

In my original data I changed the variable path so that it includes the path up to .xls, and then in the macro I did:

 

datafile='&the_path&..xls'

 

and several other modifications, but it didn't work, I still get the message that the physical file doesn't exist

ballardw
Super User

Are you running on a server version of SAS and trying to read files on your local harddrive? The server likely does not have a way of looking at your hard drive.

 

 

ilikesas
Barite | Level 11

The SAS is on my computer and so are all the files, and I can create a macro that imports all the files if they are in the same folder

ballardw
Super User

 

 

datafile='&the_path&..xls'

 

should be

datafile= "&the_path&..xls"

 

macro variables do not resolve within single quotes.

 

Run your macro with option mprint symbolgen; to see the code generated an resolved.

ilikesas
Barite | Level 11

now it says that the data C:\Documents.xls doesn't exist, so it truncated the path to the first word

Reeza
Super User

Yes, your scan function - why are you doing this? - doesn't make sense.

 

    %let the_path= %scan(&file_path, &k);
	%let name= %scan(&file_name, &k);
ilikesas
Barite | Level 11

what I want to do here is with each iteration of k, extract the kth path from the list of paths and the kth name from the list of names.

 

Then in the kth proc import I imput the kth path to extract the file, and do out the kth name to give it that name in SAS

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
  • 25 replies
  • 1404 views
  • 7 likes
  • 3 in conversation