Convert A DBF or Excel file to a SAS data set

SAS System for Windows provides a very useful procedure, PROC DBF, for converting a .dbf file or an Excel (.xls) file to a SAS data set. Column labels in the first row of the file will be converted as SAS variable names. PROC DBF is not available on Unix.

If the file you want to convert is an Excel file, first save it as a .dbf file. From the EXCEL File menu, choose the Save As command and select DBF 4 (dBASE IV (*.dbf)) for the "Save as type" of file. If the column of EXCEL cells is formatted as text, the conversion creates character variables. When the column of cells is defined by the default "general" format and the entries are numeric, the conversion creates numeric variables. If you aren't sure how a column of data will be converted, run a test on a few rows and use PROC FREQ and PROC CONTENTS to check the characteristics of the converted variables.

The following SAS program gives an example of PROC DBF. 

*   Program name:  C:\Fbh\saswork\conv-xls.sas ;
  
libname mydata 'c:\fbh\saswork';
filename dbffile 'c:\fbh\saswork\tst-dbf.dbf';

proc dbf db4=dbffile  out=mydata.tstxls;
run;
Use the libname and filename statements to set up your input/output file locations. In the program line, 
libname mydata 'c:\fbh\saswork';

"mydata" is a libref. Basically, it's an abbreviation to refer to the Drive:\directory\subdirectory where your converted SAS output file will be saved. Don't forget to enclose the directory name within single quotes. You can choose any libref and fileref names you wish as long as they meet SAS naming conventions. They must be eight characters or shorter, start with a letter or underscore, and contain only letters, numerals or underscores.

In the next program line,

filename dbffile 'c:\fbh\saswork\tst-dbf.dbf';

"dbffile" is the fileref that is used to refer to the input path\filename -- the file you want to convert. Again, enclose the full filename within single quotes.

Finally, in the procedure line,

proc dbf db4=dbffile  out=mydata.tstxls;

enter the fileref (dbffile) after db4=

and following out= enter the libref (mydata), a period, and the name of the output file. A file extension is not necessary. SAS will automatically append the file extension .sd2 to the converted output file it creates. The path\filename for the output from the above example would be:

     c:\fbh\saswork\tstxls.sd2

The SAS log will indicate the number of observations written to the output SAS data set.


NEW PSC blog

Recent resources, events, news

New Publications

Knodel et al. "Gendered Housework in Vietnam." PSC Research Report.

Couper. Designing Effective Web Surveys

Next Brown Bag

Monday, Oct 13
Jamie Jones
"Fertility & Risk Aversion on Utah Frontier"

W A R N I N G

If you are reading this, it may be that you are using rather old web browsing software that does not support modern international Web technology standards. For a better experience of the Web and this site in particular, please upgrade your web browser software today. The following are good choices: Firefox 2; Opera 9; Safari 3.