dBase DBF Files :: SAS/ACCESS(R) 9.3 Interface to PC Files: Reference

dBase DBF Files Essentials

This section introduces dBase DBF files. It focuses on the terms and concepts that help you use SAS/ACCESS Interface to PC Files. For information about Visual FoxPro, see dBase DBF MEMO Files.

DBF files are in a file format that dBASE creates. dBASE is a relational database management system for PC systems. DBF files can be created using a variety of PC software programs, such as Microsoft Excel.

A DBF file contains data that is organized in a tabular format of database fields and records. Each database field can contain one type of data, and each record can hold one data value for each field. This picture illustrates four database fields from Customer.DBF and highlights a database field and a record.

Database Field and Record

DBF Data Types

Every field in a DBF file has a name and a data type. The data type tells how much physical storage to set aside for the database field and the form in which the data is stored. This list describes each data type.

CHARACTER (N)

specifies a field for character string data. The maximum length of N is 254 characters. Characters can be letters, digits, spaces, or special characters.

ALIAS: CHAR

NUMERIC ( N, n)

specifies a decimal number. The N value is the total number of digits that are used to express the value (precision). The n value is the number of digits following the decimal point (scale). The maximum values allowed depend on which software product you are using.

dBase Maximum Numeric Values

dBASE Version	Maximum Numeric (N, n) Values
dBASE II	16, 14
dBASE III	19, 15
dBASE III PLUS	19, 15
dBASE IV	20, 18
dBASE 5.0	20, 18

Numeric field types always preserve the precision of their original numbers. However, SAS stores all numbers internally as double-precision, floating-point numbers so their precision is limited to 16 digits.

Note: If every available digit in a DBF file field is filled with a 9 , SAS interprets the value of the field as missing. If a field in SAS indicates a missing value (represented by a period), SAS writes a nine for each available digit in the corresponding DBF file database field. While in a SAS session a value is represented as missing.:

FLOAT N, n)

specifies a floating-point binary number that is available in dBASE IV and later versions. The maximum N, n value for Float is 20,18. Check with the documentation that comes with other software products that you might be using to create DBF files to determine whether those products support floating-point binary numbers.

DATE

specifies a date value in a format that has numbers and a character value to separate the month, day, and year. The default format is mm/dd/yy. For example, 02/20/95 for February 20, 1995.

Dates in DBF files can be subtracted from one another, with the result being the number of days between the two dates. A number (of days) can also be added to a date, with the result being a date.

LOGICAL

specifies a type that answers a Yes | No or True | False question for each row in a file. This type is 1 byte long and accepts these character values: Y, y, N, n, T, t, F, and f.

Note: dBASE also has data types called Memo, General, binary, and OLE. These data types are stored in an associated memo text file (a DBT file). These data types are not supported in theSAS/ACCESS Interface to PC Files.

Setting Environment Variables and System Options

MISSING VALUES

Missing numeric values are filled in with blanks by default. The DBFMISCH environment variable is used to change the default by specifying the character that the interface to DBF files uses to fill missing numeric fields. If you try to write a SAS file with a missing numeric variable to a DBF file, the corresponding DBF field is filled with the DBFMISCH character. Conversely, any numeric or float field in a DBF file that contains the DBFMISCH character is treated as a missing value when SAS read it.

You set the DBFMISCH environment variable in the SAS configuration file by using this syntax:

-set
                                 DBFMISCH value

Valid values:

any single character

Type in any single character. For example, to fill missing numeric values with the character '9', enter -set DBFMISCH 9.

NULLS

To replace missing numeric values with binary zeros, enter -set DBFMISCH NULLS.

BLANKS

To replace missing numeric values with blanks, enter -set DBFMISCH BLANKS.

DECIMAL SEPARATOR

Although the United States uses a decimal separator, other countries use different symbol characters. For example, some European countries use a comma. You must set the CTRYDECIMALSEPARATOR= system option to enable users to import or export data that is saved with a different decimal.

CTRYDECIMALSEPARATOR= system option syntax: OPTIONS CTRYDECIMALSEPARATOR= value;

Any character is valid. For example, to set a comma as the decimal separator submit this statement in SAS. OPTIONS CTRYDECIMALSEPARATOR=',' ;

This code uses the period character instead of the comma character. To save the numeric values in an exported DBF file while running SAS in a German environment.

OPTIONS CTRYDECIMALSEPARATOR='.';
 PROC EXPORT DATA = sashelp.class    
 FILE= 'c:\temp\class.dbf'  
 DBMS=DBF REPLACE;
 RUN;

Supported SAS IMPORT and EXPORT Procedure Statements

The IMPORT | EXPORT method uses DBF file formats to access data in DBF Files on Linux, UNIX, and Microsoft Windows operating environments.

The method imports data from DBF files in versions 3, 4, and 5 formats. It exports data to DBF files with version 5 formats.

See Example 1: Export Data to a DBF File from a SAS Data Set for additional information.

IMPORT and EXPORT Procedure Statements for DBF Files

Data Source	Syntax	Valid Values	Default Value	PROC IMPORT	PROC EXPORT
DBF	DBENCODING	Encoding-value	Current SAS session encoding	Yes	Yes
DBF	GETDELETED	Yes \| No	Yes	Yes	No

DBENCODING = 12–byte SAS encoding-value: indicates the encoding used to save data in DBF files. Encoding maps each character in a character set to a unique numeric representation, which results in a table of code points. A single character can have different numeric representations in different encodings.

For example, some DBF files were saved with pcoem850 encoding. When you are importing these DBF files in Microsoft Windows, specify:

DBENCODING=pcoem850.

Interaction: The IMPORT procedure reads and transcodes data from pcoem850 to Microsoft Windows default WLATIN1.

Note: Refer to the SAS NLS User's Guide for information about transcoding and valid encoding values.

GETDELETED= YES | NO: indicates whether to write rows to the SAS data sets that are marked for deletion but have not been purged.

YES writes rows to the SAS data sets that are marked for deletion and have not been purged.

NO does not write rows to the SAS data sets that are marked for deletion and have not been purged.

Alias: GETDEL

Example 1: Export Data to a DBF File from a SAS Data Set

This example exports data to a DBF file, named test.dbf, from a SAS data set named SDF.EMPLOYEE, with a WHERE condition in the data set option.

LIBNAME SDF “&sasdir”;
PROC EXPORT DATA=SDF.EMPLOYEE (WHERE=(HIREDATE is not missing))
            OUTFILE=”&tmpdir.test.dbf”
            DBMS=DBF REPLACE;
RUN;

Example 2: Import Data from a DBF File into a SAS Data Set

This example imports data from a DBF file named invoice.dbf into SAS data set named TEST5. The data is imported without the DELETE flag field.

PROC IMPORT OUT=WORK.TEST5
            DATAFILE="&dbfdir.Invoice.dbf"
            DBMS=DBF REPLACE;
   GETDEL=NO;
RUN;

Example 3: Export Data to a DBF File from a SAS Data Set Using Encoding

This example exports data from a SAS data set named SDF.ORDERS to a DBF file named Oem850.dbf. The procedure translates SAS data from its current session encoding, to pcoem850 encoding and writes it to the DBF file.

PROC EXPORT DATA=SDF.ORDERS
            OUTFILE="&dbfdir.Oem850.dbf"
            DBMS=DBF REPLACE;
   DBENCODING=pcoem850;
RUN;

Example 4: Import and Translate Data from a DBF File

This example imports data from a DBF file named Oem850.dbf, which was saved with pcoem850 encoding. The procedure reads in the data and translates it from pcoem850 encoding to current SAS session encoding.

PROC IMPORT OUT=WORK.ORDERS
            DATAFILE="&dbfdir.Oem850.dbf"
            DBMS=DBF REPLACE;
   DBENCODING=pcoem850;
RUN;