DATA Step, Macro, Functions and more

importing files from different subdirectories and positional parameters

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

importing files from different subdirectories and positional parameters

[ Edited ]

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! 


Accepted Solutions
Solution
‎03-22-2017 09:29 AM
Super User
Posts: 17,813

Re: importing files from different subdirectories and positional parameters

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


All Replies
Super User
Posts: 17,813

Re: importing files from different subdirectories and positional parameters

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. 

 

Super Contributor
Posts: 413

Re: importing files from different subdirectories and positional parameters

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! 

Super User
Posts: 10,497

Re: importing files from different subdirectories and positional parameters

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.

 

 

 

 

Super Contributor
Posts: 413

Re: importing files from different subdirectories and positional parameters

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

Super User
Posts: 10,497

Re: importing files from different subdirectories and positional parameters

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.

Super Contributor
Posts: 413

Re: importing files from different subdirectories and positional parameters

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! 

Super User
Posts: 17,813

Re: importing files from different subdirectories and positional parameters

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
Super Contributor
Posts: 413

Re: importing files from different subdirectories and positional parameters

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

Super User
Posts: 10,497

Re: importing files from different subdirectories and positional parameters

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.

 

 

Super Contributor
Posts: 413

Re: importing files from different subdirectories and positional parameters

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

Super User
Posts: 10,497

Re: importing files from different subdirectories and positional parameters

 

 

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.

Super Contributor
Posts: 413

Re: importing files from different subdirectories and positional parameters

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

Super User
Posts: 17,813

Re: importing files from different subdirectories and positional parameters

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);
Super Contributor
Posts: 413

Re: importing files from different subdirectories and positional parameters

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 25 replies
  • 162 views
  • 7 likes
  • 3 in conversation