BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
odmhx
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Daryl
SAS Employee

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

7 REPLIES 7
goladin
Calcite | Level 5

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

odmhx
Calcite | Level 5

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

DBailey
Lapis Lazuli | Level 10

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?

Daryl
SAS Employee

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;

odmhx
Calcite | Level 5

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

Ksharp
Super User

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

odmhx
Calcite | Level 5

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. 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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