SAS/ACCESS Software for PC File Formats: Reference

Understanding DBF Essentials

This chapter introduces SAS System users to DBF files, which can be created using a variety of microcomputer software programs. DBF files are a file format created by dBASE, a relational database management system for microcomputer systems.

This chapter focuses on the terms and concepts that help you access DBF files with SAS/ACCESS software. Then it describes DBF-specific statements you use in the ACCESS and DBLOAD procedures. Finally, it also contains a section on how the SAS/ACCESS interface works.

Note: The SAS/ACCESS interface cannot access DBF files created by Visual dBASE 7. [cautionend]

The SAS/ACCESS interface works with DBF files that are created by dBASE (II, III, III PLUS, IV, and 5.0) and with DBF files that are created by other software products.

As an introduction to DBF files, this chapter describes DBF files that are created using dBASE 5.0, rather than describing each version of dBASE and the differences among them.(footnote 1) If you want more information on a dBASE concept or term, see the dBASE documentation packaged with your system.

DBF Files

A DBF file contains data that are 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. DBF File illustrates four database fields from CUSTOMER.DBF and highlights a database field and a record.

The SAS/ACCESS interface uses database files that have a .DBF extension. A DBF file consists of a specific number of database fields and some number of records. DBF files are one kind of file that you can select in a catalog. You can create DBF files in a number of ways in dBASE, including using the CREATE command. See your dBASE or other software products' documentation for information about creating DBF files and assigning field names, field types, and other attributes.

DBF File

[IMAGE]

The ACCESS procedure uses SAS/ACCESS descriptor files to reference DBF files for reading or extracting data. It cannot use any dBASE indexes or indexes created by other software products that are defined on the fields in a DBF file. You can use the view descriptors you create to update DBF data. You can use the DBLOAD procedure to create and load DBF files.

The ACCESS procedure cannot reference DBF files that are secured through encryption. Like other files, DBF files are subject to any security restrictions imposed by the operating system or network (if applicable).

DBF File Naming Conventions

Filenames must also follow operating-system specific conventions, so check the documentation that comes with your dBASE product or other software products for further information. The following conventions apply to DBF filenames and field names:

Under Windows 95, Windows 98, Windows NT, and OS/2, the ACCESS and DBLOAD procedures support long names that are specified in the PATH= statement (such as path='c:\sasdemo\library\customer99.dbf';) However, some applications that support dBASE files might not accept files with long names.
Filenames or field names start with a letter, and they can contain any combination of the letters A through Z, the digits 0 through 9, the colon (:) (in dBASE II field names only), and the underscore (_).
Database field names can be from one to ten characters long. Each field in a DBF file has a unique name.
Filenames or field names are not case sensitive; that is, CUSTOMER is the same as Customer. Field names typed in lowercase are changed to uppercase on the display.

DBF File 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 are stored. The following section lists and 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. You can abbreviate character to char in your programs.

Numeric(N,n)

specifies a packed decimal number, that is, a Binary Coded Decimal number. The N value is the total number of digits (precision), and the n value is the number of digits to the right of the decimal point (scale). The maximum values allowed depend on the software product you are using. For dBASE products, the maximum values allowed are

dBASE Version N,n

dBASE II 16,14

dBASE III 19,15

dBASE III PLUS 19,15

dBASE IV 20,18

dBASE 5.0 20,18

dBASE Version	N,n
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, the SAS System 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, the value of the field is interpreted as missing by the SAS System. If a field in the SAS System indicates a missing value (represented by a period), the SAS System writes a 9 for each available digit in the corresponding DBF file database field. While in a SAS session, if you fill every available digit in a DBF file field with 9s, scroll from the field, and return to the field, the value is represented as missing. [cautionend]

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 you may be using to create DBF files to determine if 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 record in a file. This type is 1 byte long and accepts the following character values: Y, y, N, n, T, t, F, and f.

dBASE also has data types called Memo, General, binary, and OLE, which are stored in an associated memo text file (called a DBT file), but these data types are not supported in the SAS/ACCESS interface to PC file formats.

How the SAS/ACCESS Interface Works describes how the DBLOAD procedure determines data types when creating DBF files.

ACCESS Procedure Data Conversions

The table below shows the default SAS System variable formats that the ACCESS procedure assigns to each DBF file data type. If DBF file data fall outside of the valid SAS data ranges, you get an error message in the SAS log when you try to read the data.

Default SAS System Variable Formats for DBF File Data Types
DBF File Data Type SAS Variable Format

Character(n) $n .(n <= 200)
$200. (n > 200)

Numeric(N,n) (N,n)

Float(N,n)^* (N,n)

Date MMDDYY8.

Logical $1.

*This data type applies to dBASE IV and later. Check with other software products' documentation to see if this data type applies.

***Default SAS System Variable Formats for DBF File Data Types***
DBF File Data Type	SAS Variable Format
Character(n)	$n .(n <= 200) $200. (n > 200)
Numeric(N,n)	(N,n)
Float(N,n)^*	(N,n)
Date	MMDDYY8.
Logical	$1.
*This data type applies to dBASE IV and later. Check with other software products' documentation to see if this data type applies.

FOOTNOTE 1: The term dBASE refers to dBASE 5.0 for Windows unless otherwise noted.

Chapter Contents
Previous
Next
Top of Page