topic Re: importing files from different subdirectories and positional parameters in SAS Programming

importing files from different subdirectories and positional parameters

ilikesas — Tue, 21 Mar 2017 19:34:16 GMT

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!

Re: importing files from different subdirectories and positional parameters

Reeza — Tue, 21 Mar 2017 19:42:40 GMT

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.

Re: importing files from different subdirectories and positional parameters

ilikesas — Tue, 21 Mar 2017 19:49:24 GMT

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!

Re: importing files from different subdirectories and positional parameters

ballardw — Tue, 21 Mar 2017 20:03:21 GMT

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.

Re: importing files from different subdirectories and positional parameters

ilikesas — Tue, 21 Mar 2017 20:06:58 GMT

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

Re: importing files from different subdirectories and positional parameters

ballardw — Tue, 21 Mar 2017 20:09:59 GMT

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.

Re: importing files from different subdirectories and positional parameters

ilikesas — Tue, 21 Mar 2017 20:38:19 GMT

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!

Re: importing files from different subdirectories and positional parameters

Reeza — Tue, 21 Mar 2017 21:21:03 GMT

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

Re: importing files from different subdirectories and positional parameters

ilikesas — Tue, 21 Mar 2017 21:52:41 GMT

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

Re: importing files from different subdirectories and positional parameters

ballardw — Tue, 21 Mar 2017 22:06:48 GMT

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.

Re: importing files from different subdirectories and positional parameters

Reeza — Tue, 21 Mar 2017 22:09:49 GMT

Make sure the file is named exactly as indicated, if you're on Unix the path may be case sensitive.

Also, if you're not working on SAS server (ie EG/Studio), the server won't have access to your desktop.

Try a plain jane proc import with all values hardcoded, no macro variables or macro, and see if that works.

Re: importing files from different subdirectories and positional parameters

ilikesas — Tue, 21 Mar 2017 22:13:50 GMT

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

Re: importing files from different subdirectories and positional parameters

ilikesas — Tue, 21 Mar 2017 22:19:32 GMT

The SAS is on my computer and so are the files. If all the files are in the same directory I can import them with a macro.

Also, first I used a macro to extract all the file paths and names into a table, and from that table I want to get the paths into a another macro that will import those files. The file paths and names extraction macro is actually a solution to one of my other questions, here I put it in case it i sof interest:

%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;

      %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;
%list_files(C:\Documents and Settings\HP_Administrator\Desktop\files,xls)

"files" is the folder that contains the other folders that contain the xls files

Re: importing files from different subdirectories and positional parameters

ballardw — Tue, 21 Mar 2017 22:22:26 GMT

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.

Re: importing files from different subdirectories and positional parameters

ilikesas — Tue, 21 Mar 2017 22:31:05 GMT

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

Re: importing files from different subdirectories and positional parameters

Reeza — Tue, 21 Mar 2017 22:44:58 GMT

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);

Re: importing files from different subdirectories and positional parameters

ilikesas — Tue, 21 Mar 2017 22:50:45 GMT

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

Re: importing files from different subdirectories and positional parameters

Reeza — Tue, 21 Mar 2017 23:02:46 GMT

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;

Re: importing files from different subdirectories and positional parameters

Reeza — Tue, 21 Mar 2017 23:04:10 GMT

@ilikesas wrote:

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

But you don't have that in a macro list, you have that in a DATASET. You need to access the kth element of a data set, so you need to call the macro for each row.

Re: importing files from different subdirectories and positional parameters

ilikesas — Tue, 21 Mar 2017 23:29:00 GMT

Thanks for the code Reeza!!!

Just a small question:

when I was doing my previous code, I was hoping to append all the imported files together, and I wanted to do this within a macro in such a way that when a file is imported it is directly appended to the master data and then it is immediately erased - first importing all the files and then appending them all together might take a very long time if there are many files.