Help using Base SAS procedures

How to read in files from different directories

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

How to read in files from different directories

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


Accepted Solutions
Solution
‎06-27-2011 03:44 PM
SAS Employee
Posts: 27

How to read in files from different directories

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;

View solution in original post


All Replies
Contributor
Posts: 24

How to read in files from different directories

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;

M

Contributor
Posts: 35

How to read in files from different directories

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

Super Contributor
Posts: 578

How to read in files from different directories

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?

Solution
‎06-27-2011 03:44 PM
SAS Employee
Posts: 27

How to read in files from different directories

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;

Contributor
Posts: 35

Re: How to read in files from different directories

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

Grand Advisor
Posts: 9,458

How to read in files from different directories

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

Contributor
Posts: 35

Re: How to read in files from different directories

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. 

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 275 views
  • 4 likes
  • 5 in conversation