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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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