importing files from different subdirectories and positional parameters

[Reeza · Accepted solution]

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;

[ilikesas]

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.

[Reeza]

You can modify the import macro with an append option or just append. I will say though, if you're importing Excel files, I guarantee (and I'd bet on it) that the will process fail somewhere. Proc Import guesses at types and it's not always correct or what you expect. You'll end up with the same variable having different types in this process and it will break your append process. If your files are actually alike, I would add a step to covert all from xls to csv, via a vb script though. 

%macro import_file(path, file_name, dataset_name );

	proc import 
		datafile="&path.\&file_name."
		dbms=xlsx
		out=&dataset_name replace;
	run;
    
       proc append base=master data=&dataset_name force;
       run;

       proc sql;
              drop table &dataset_name;
       quit;
     

%mend;

Appending all at the end is trivial if you've used a naming convention, but like I mentioned above, it will have other issues.

[ilikesas]

Hi Reeza,

I just realized that the code deosn't import all of the files... It imported some of the files, but didn't import the other, more "hidden" files

Here is my original file structure (the files themselves consist of only 2 variables and 1 observation for simplicity) if you would like to replicate it:

C:\Documents and Settings\HP_Administrator\Desktop\files\file2.xls

C:\Documents and Settings\HP_Administrator\Desktop\files\firstfile.xls

C:\Documents and Settings\HP_Administrator\Desktop\files\New Folder\thirdfile.xls

C:\Documents and Settings\HP_Administrator\Desktop\files\New Folder2\fourth file.xls

Thanks!

[Reeza]

---

@ilikesas wrote:

Hi Reeza,

I just realized that the code deosn't import all of the files... It imported some of the files, but didn't import the other, more "hidden" files

Here is my original file structure (the files themselves consist of only 2 variables and 1 observation for simplicity) if you would like to replicate it:

C:\Documents and Settings\HP_Administrator\Desktop\files\file2.xls

C:\Documents and Settings\HP_Administrator\Desktop\files\firstfile.xls

C:\Documents and Settings\HP_Administrator\Desktop\files\New Folder\thirdfile.xls

C:\Documents and Settings\HP_Administrator\Desktop\files\New Folder2\fourth file.xls

Thanks!

---

I tested this and it worked with several nested folders, so no idea what you mean by 'hidden' files. Where is the process failing? What is the error message, turn on all macro debugging options? 

[ilikesas]

I didn't get "thirdfile" and "fourth file". The data table icon is shown, but when I click on it it says that there are 0 observations in these tables

[Reeza]

I need to know what step it failed at. Trace the process and isolate where it's failing.

[ilikesas]

IT WORKED!!!

Its just that instead of naming the files test1 test2 ... I wanted to name them by their real names. But the thingis that for the "fourth file" I was getting 2 empty data sets, namely "fourth" and "file" - I guess that this is because of the empty space, so in case when there are empty spaces I just replaced them with underscores. I also added a variable in each file that will contan the file's name like this I can know which data came from which file. Here are the small modifications that I did:

%macro import_file(path, file_name, dataset_name );

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

data &dataset_name;
	set &dataset_name indsname=source;
dsname = scan(source,2,'.');
run;

       proc append base=master data=&dataset_name force;
       run;

       proc sql;
              drop table &dataset_name;
       quit;
     

%mend;

data list;
set list;
file_name = translate(trim(the_name),'_',' ');
run;

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

Thanks Reeza for the help!

If you like, please put all your steps into one post (including the appending step) so that I can mark it as the solution!!!

[Reeza]

---

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

[Reeza]

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.

[ilikesas]

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