Creating a data set using files with the same name in many directories

[art297]

Bruce: I agree with data_null_ that Tom's code is definitely better than what I had suggested and should be the one you mark as being correct. I didn't realize that SAS now allows full paths in a set statement and their use definitely simplifies identifying the directory names.

Using the same approach I mentioned in my last post, if you run the first two lines of code, below, from your window's command prompt, it will create the list of files:

cd c:\

dir dec30.sas7bdat /s /b > c:\temp\temp.txt

The following datastep will create a file containing all of the desired files and paths:

data files ;

  infile "c:\temp\temp.txt" truncover ;

  input fname $256.;

run;

The following proc sql call will create a macro variable that will contain the string that can be used to set all of the files:

proc sql noprint ;

  select quote(trim(fname)) into :flist separated by ' '

    from files

  ;

quit;

Finally, the following step will concatenate all of the files together including variables that indicate the filenames and their full paths:

data want ;

   length dsname $256 folder memname $32 ;

   set &flist indsname=dsname ;

   folder = scan(dsname,-2,'\.') ;

   memname = scan(dsname,-1,'\.');

run;

[brucehughw]

The code Tom sent is mostly working.  In the last step I get an error message because the same variable is a character in some files and numeric in others. I know how to fix that for individual files, but would like to fix all the files using a macro. The first two files listed in the &flist macro variable are the following:

"\\VNTSCEX.LOCAL\DFS\SPECIAL\SAS-STORAGE\WILSON, BRUCE\WINTERWX\SAMPLE DATA\ACY\Dec

23\acy12232014_fund.sas7bdat" "\\VNTSCEX.LOCAL\DFS\SPECIAL\SAS-STORAGE\WILSON, BRUCE\WINTERWX\SAMPLE

DATA\ACY\Dec 24\acy12242014_fund.sas7bdat" "\\VNTSCEX.LOCAL\DFS\SPECIAL\SAS-STORAGE\WILSON,

BRUCE\WINTERWX\SAMPLE DATA\ACY\Dec 25\acy12252014_fund.sas7bdat"

Can someone please tell me how to iterate over &flist so that I can open each data set, repair it (I can handle that part), and save it was a new, cleaner data set, named something like acy_12232014_fund_clean (i.e., I just append something to the original name).

Thanks very much, Bruce