Search Data Services
Meta Search
search across all the following databases:
Data Catalog
Data and documentation
KnowledgeBase
Common questions and answers.
Resources
Entire collection of data resources.
Latest Data News
Imagining a Census Survey Without a Mandate
Lessons from North of the Border
Nerd Alert: Dictionary of Numbers
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.