Iterate over a list of files in a directory and extract the file type

[ABurn]

Hi, new SAS user here, hope you guys can help!

I have a list of files in a directory ending in either .txt or .dbf. I would like to read in the list of names, then import the data appropriately depending on the file type. I have managed to get the list of names, and can even use substr(file_name,max(1,length(file_name)-2)) to get the extension. What i can't do is iterate over this list of extensions in an if statement to specify the input type.

It seems the %scan function is not doing what I need it to? But I'm not sure why. Can anyone help/point me in the right direction?

Here is the code that I have:

filename DIRLIST pipe 'dir "M:\R\SASDatasets\*" /b ';

data dirlist ;
  infile dirlist lrecl=200 truncover;
  input file_name $100.;
run;

data _null_;
  set dirlist end=end;
  count+1;
  call symputx('read'||put(count,4.-l),cats('M:\R\SASDatasets\',file_name));
  call symputx('dset'||put(count,4.-l),scan(file_name,1,'.'));
  if end then call symputx('max',count);
run;

%macro readin;
data file_type;
  set dirlist;
  file_type = substr(file_name,max(1,length(file_name)-2));
  drop file_name;
run;

%do i=1 %to &max;
  %let type = %scan(file_type, &i);

  %if &type='dbf' %then %do;
     proc import datafile="&&read&i"
       out=df.&&dset&i dbms=dbf;
     run;
     %end;
  %else %do;
      proc import datafile="&&read&i"
        out=df.&&dset&i_label
        dbms=dlm
        replace;
        delimiter='09'x;
      run;
   %end;
%end;
%mend readin;

%readin;

[Reeza]

%if &type='dbf' -> remove the quotes and ensure that case is not different, ie DBF does not equal dbf.

[ABurn]

Thanks, but now it is saying:

"WARNING: Apparent symbolic reference I_LABEL not resolved.

WARNING: Apparent symbolic reference DSET not resolved.

ERROR: "DF. is not a valid name"

Event though I defined df as my library 😞

[Reeza]

Right, well you declared DSET1, DSET2 etc, not DSET so there is no macro variable called DSET. Check what you need to get it resolved, ie you may need to add a period or extra &. 

Honestly, not a huge fan of macro loops, I just call it multiple times with explicit parameters, it's a lot cleaner IMO and makes it more reusable in the future than just a single process. 

If your text files have the same format by the way, you can import them all at once into a single file and then import each dbf separately.

Here's an example that imports all XLSX files using that approach. 

https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

It should be relatively easy to modify yours to that approach if you want, and should be quick. 

You already have the file list so you can skip that part. 

[ABurn]

Thanks! I have got it to work 🙂 I still used the loop, but I just split it into 2 macros so I didn't need to find the file type.