topic Re: Issue with importing all excel files in folders in SAS Programming

Issue with importing all excel files in folders

imdickson — Fri, 21 Sep 2018 07:43:41 GMT

Hi everyone. I currently have all the excel files in Linux server. What i want to do is loading all excel files into SAS and create dataset based on the original file name.

I have read a lot of online tutorial and most of them are only on single excel files. However, i managed to find some about loading multiple excel in multiple folders.

I actually copied the code from here:

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

However, i am getting the error of directory or path does not exist when the same exact path is accessible using mobaxterm.

My code below(Slight change on the path)

%*Creates a list of all files in the DIR directory with the specified extension (EXT);
%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 to import a single file, using the path, filename and an output dataset name must be specified;
%macro import_file(path, file_name, dataset_name );

	proc import 
		datafile="&source./tnbt/Profile_List_Files/Central/ABBA/&file_name."
		dbms=xlsx
		out=&dataset_name replace;
	run;

%mend;

*Create the list of files, in this case all XLSX files;
%list_files(&source./tnbt/Profile_List_Files/Central/ABBA/, xlsx);

%*Call macro once for each entry in the list table created from the %list_files() macro;
data _null_;
	set list;
	string = catt('%import_file(', dir, ', ',  name,', ', catt('test', put(_n_, z2.)), ');');
	call execute (string);
run;

However in the log, it says this:

Directory /sasdata/source/tnbt/Profile_List_Files/Central/ABBA/ cannot be open or does not exist

I have no clue what is going on as I am pretty certain that the path is 100% accessible. What could be wrong here?

Re: Issue with importing all excel files in folders

RW9 — Fri, 21 Sep 2018 08:14:09 GMT

First off, why is the data in Excel - this is a really bad format for data, and if your using linux will mean that you need to use pcfiles server of something like that to even use it. Bin that format as quick as possible and move to open file formats such as csv or xml and your life will be a lot easier, for instance, if these were csv files you could read them all in in one step (assuming structure is the same throughout).

Now with your question here, #

Directory /sasdata/source/tnbt/Profile_List_Files/Central/ABBA/ cannot be open or does not exist

Does that folder exist, and is it available to SAS. I can't check this for you, you need to go into SAS and try creating a libname to that area. Just because a folder exists does not mean that SAS can see or have access to it.

Re: Issue with importing all excel files in folders

imdickson — Fri, 21 Sep 2018 08:47:17 GMT

Hi RW9,

the data was maintained by other team......

I've just verified with my team and SAS Admin has "full" access right(i mean 755) to the path and files in it. They tried creating a library and point to that path and it was working.

RW9, do you have any tips for me or any other code that i can try that will read excels in either mutiple folders or just a single folder?

I would love to look at it and try out.

Thanks again RW9.

Re: Issue with importing all excel files in folders

RW9 — Fri, 21 Sep 2018 09:45:58 GMT

So if "and SAS Admin has "full" access right(i mean 755) to the path and files in it. They tried creating a library and point to that path and it was working." - is true, then it seems that you don't have access to that area, and they need to grant you or your SAS session the correct access. Changing code will not change this fact, the SAS system needs to have access to the area.

As for how I would do it, dir list in, call execute out (note this is windows, so you would need to change the pipe to "ls .." per linux commands):

filename tmp pipe 'dir "c:/datalocation/*.xlsx" /b';

data _null_;
  length fname $200;
  infile tmp;
  input fname $;
  call execute(cats('proc import datafile="c:/datalocation/',fname,' out=want',put(_n_,best.),'; run;'));
run;

In this example, for each filename read in from the pipe a proc import statement is created to read in the file into wantX - X being incremental. I don't know what you want to call the output files, so I chose wantX, you would need to update that and make sure any name is SAS compliant.

Re: Issue with importing all excel files in folders

imdickson — Fri, 21 Sep 2018 09:54:20 GMT

Thanks @RW9