How to import multiple .dbf files in SAS

Question

%let dirname = C:\Users\data;
 filename DIRLIST pipe 'dir/B &dirname\*.dbf';

 /* Create a data set with one observation for each file name */
 data dirlist;
     length fname $8.; 
     infile dirlist length=reclen;
     input fname $8.;
 run;

 data all_text (drop=fname);
 set dirlist;
 filepath = "&dirname\"||fname||".dbf";
 infile dummy filevar = filepath length=reclen end=done missover;
 do while(not done);    
 INPUT
 F1               : 2.
 F2              : 2.
 F3               : 2.
 F4               : 10.
 F5               : 4.;
 output;        
 end;    
 run;

The problem is that it is only reading the first line of each files and not the whole file before moving to the next. Also variable F1 are shown as missing.

Suggestions are welcome

I'm trying to import multiple .dbf files in SAS using macros and pipe. I have done this before for different file format using the standard code available online but i'm not been able to do it for .dbf files. My data structure is as follows which is same for all the files variables: A - numeric length 2; B - numeric length 2; c - numeric length 2; D - max numeric length 10; E - numeric length 4; Also each filename follows xxx_xxxx.dbf — sayan de sarkar, Nov 13 '16 at 15:44
Your code is for reading text files. To read DBF files use PROC IMPORT instead of a DATA STEP. — Tom, Nov 13 '16 at 16:21
i believe i can use PROC IMPORT for one file at a time. But i need to import more than 300 .dbf files and append together into a single file. sample code of doing it using PROC IMPORT will be very helpful — sayan de sarkar, Nov 13 '16 at 16:32

score 1 · Answer 1 · answered Nov 13 '16 at 21:52

Import them one by one and then combine them.

%let dirname = C:\Users\data; 
data filelist ;
  infile "dir /b &dirname\*.dbf" pipe truncover end=eof;
  fileno + 1;  
  input fname $256. ;
  tempname = 'temp'||put(fileno,z4.);
  call execute(catx(' ','proc import replace dbms=dbf'
       ,'out=',tempname,'datafile=',quote(trim(fname)),';run;'
  ));
  if eof then call symputx('lastname',tempname);
run;
data want ;
  set temp0001-&lastname;
run;

score 1 · Accepted Answer · answered Nov 14 '16 at 00:27

So a standard proc import would be:

 proc import out=sample1 datafile="path to dbf file.dbf" dbms=DBF replace; 
 run;

The problem now, is how to generate this set of code for every file in your file list. Using the CALL EXECUTE statement from @Tom is your best bet. You call also create a small macro and call it for each filename, using CALL EXECUTE. If you're new to SAS this can be easier to understand.

*Create a macro that imports the DBF

%macro import_dbf(input= , output=);

  proc import out=&out datafile="&output" dbms=DBF replace; 
 run;

%mend;

Then call macro from dataset. I'm naming the datasets DBF001, DBF0002 etc.

%let dirname=C:\_localdata;

data dirlist;
    informat fname $20.;
    input fname;
    cards;
    data1.dbf
    data2.dbf
    data3.dbf
    data4.dbf
    ;
run;

data out;
    set dirlist;
    str=catt('%import_dbf(input="', "&dirname", '\', fname, '", output=dbf', 
        put(_n_, z4.), ');');
run;

proc print data=out;
run;

How to import multiple .dbf files in SAS

2 Answers2