topic How to read in files from different directories in SAS Procedures

How to read in files from different directories

odmhx — Mon, 27 Jun 2011 14:49:02 GMT

I need to read multiple flat files from different directories like this:

Directories are under e:\masterdirectory\. There are 2 levels under them, each named differently.

I want to make the following working, but couldn't. Can anyone help?

filename 'e:\masterdirectory\*\*\*weeklyresults.weekof*';

Thank you

How to read in files from different directories

goladin — Mon, 27 Jun 2011 16:22:00 GMT

Hi,

Perhaps you can create a simple dataset with all the direction and use call execute to execute them.

data dir;

input direct $50.;

datalines;

e:\masterdirectory\a\b

e:\masterdirectory\a\c

e:\masterdirectory\a\v

;

run;

data _null_;

set dir;

call execute('filename A'||compress(_N_)||' '||compress(direct)||'\weeklyresults.weekof');

run;

How to read in files from different directories

odmhx — Mon, 27 Jun 2011 16:50:11 GMT

Thank you for your suggestion. However, the directory names are changed from week to week. What doesn't change is the masterdirectory.

How to read in files from different directories

DBailey — Mon, 27 Jun 2011 18:13:47 GMT

Do you know what the directory names will be prior to importing? Is there a pattern you so that you could predict what the fully qualifed file names would be?

How to read in files from different directories

Daryl — Mon, 27 Jun 2011 19:44:24 GMT

You can do a recursive directory listing and pipe it into SAS as input. Just change C:\temp to your top level directory. This will list every file under that directory and capture the result in a SAS dataset. You can then walk through the dataset to create the filenames. I'm sure there is a more elegant approach, but this code works on my PC:

filename DIRLIST pipe 'dir "C:\temp" /s /b';

data dirlist ;
     length buffer $256 ;
     infile dirlist length=reclen ;
     input buffer $varying256. reclen ;
run ;

proc sql noprint;
 select count(*) into: row_count from dirlist;
quit;
run;

%put Row Count is &row_count..;

%macro fileloop;
 %do i = 1 %to &row_count;
  data _null_;
   ptr = &i;
   set dirlist point=ptr;
   call symput('filenm',buffer);
   stop;
  run;
  %put "I is " &i.;
  filename F%trim(&i) "&filenm";
 %end;
 %end;
%mend;
%fileloop;

filename _all_ list;

How to read in files from different directories

Ksharp — Tue, 28 Jun 2011 03:28:04 GMT

My code search xls files in very sub-directory under c:\temp ,and convert them into csv at sub-directory respectively.

filename _xls pipe 'dir c:\temp\*.xls /s';

data xls_file;

infile _xls length=len;

input whole $varying200. len;

retain directory;

length file_name $ 200;

if strip(whole) eq: 'c:\temp' thendirectory=scan(whole,1,' ');

else if scan(whole,-1,'.') eq 'xls' then do;

_name=scan(trim(whole),-1,' ');

file_name=catx('\',directory,_name);

output;

end;

keep file_name;

run;

data _null_;

set xls_file nobs=num_obs;

call symputx(cats('path',_n_),file_name);

call symputx(cats('name',_n_),scan(file_name,-2,'.\'));

call symputx('nobs',num_obs);

run;

%put _user_;

options mprint mlogic symbolgen;

%macro xls_to_csv;

%do i=1 %to&nobs;

proc import datafile="&&path&i" out=&&name&i dbms=excel replace;

getnames=yes;

mixed=yes;

run;

proc export data=&&name&ioutfile="%scan(&&path&i,1,'.').csv " dbms=csv replace;run;

%end;

%mend ;

%xls_to_csv

Ksharp

Re: How to read in files from different directories

odmhx — Thu, 30 Jun 2011 03:23:12 GMT

Thank you very much for your codes. They are very helpful. I am using them and I got them work in my program now!

Re: How to read in files from different directories

odmhx — Thu, 30 Jun 2011 03:32:37 GMT

Thank you very much for your codes. My problem is that I have two levels under main folder, which are named differently from week to week, and the flat files are also named differently from week to week.