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
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;
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
Thank you for your suggestion. However, the directory names are changed from week to week. What doesn't change is the masterdirectory.
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?
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;
Thank you very much for your codes. They are very helpful. I am using them and I got them work in my program now!
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
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 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.