SAS/ACCESS(R) 9.2 Interface to PC Files: Reference

Product	Release
SAS/ACCESS Interface to PC Files	9.2

File Format-Specific Reference for IMPORT and EXPORT Procedures

dBase DBF Files

This section introduces dBase DBF files. It focuses on the terms and concepts that help you use SAS/ACCESS Interface to PC 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 Visual FoxPro.

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. The following picture illustrates four database fields from Customer.DBF and highlights a database field and a record.

[DBF file]

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. The following 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. You can abbreviate character to char in your programs.

Numeric(N,n)

specifies a 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 as follows:

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, 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, if you fill every available digit in a DBF file field with nines, 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 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 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. These data types are stored in an associated memo text file (a DBT file), but these data types are not supported in the SAS/ACCESS Interface to PC Ffiles.

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. For example, if you try to write a SAS file with a missing numeric variable to a DBF file, the corresponding field in the DBF file would be filled with the DBFMISCH character. Conversely, any numeric or float field in a DBF file that is filled with the DBFMISCH character is treated as missing when read by SAS. You set the DBMISCH environment variable in the SAS configuration file using the following syntax: -set DBFMISCH value Here are the 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 a period is used as decimal separator in the United States, some countries might use different symbol characters. For example, some European countries use comma character as the decimal separator. To allow data saved with different decimal separator to be imported or exported correctly, you must set SAS system option, CTRYDECIMALSEPARATOR. You set CTRYDECIMALSEPARATOR option in the SAS using the following syntax: options CTRYDECIMALSEPARATOR=value; Valid values can be any character. For example, European users might set a comma as the decimal separator by submitting the following statement in SAS:

options CTRYDECIMALSEPARATOR=',' ;

Supported SAS IMPORT/EXPORT Procedure Statements

This Import/Export component uses DBF file formats to access data in DBF Files on Linux, UNIX, and Microsoft Windows operating platforms. It imports data from DBF files that were saved in Version 3, Version 4, and Version 5 formats. It exports data to DBF files with Version 5 formats.

The following table lists the statements that are available for processing DBF files in IMPORT and EXPORT procedures. See Examples of Importing and Exporting DBF Files.

IMPORT and EXPORT Procedure Statements for DBF Files
Data Source	Syntax	Valid Values	Default Value	In PROC IMPORT?	In PROC EXPORT?
DBF	DBENCODING=	Encoding-value	Current SAS session encoding	Yes	Yes
	GETDELETED=	YES \| NO	YES	Yes	No

DBENCODING='encoding-value';

Indicates the encoding used to save data in DBF files. An encoding maps each character in a character set to a unique numeric representation, which results in a table of all 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 on Microsoft Windows, specify DBENCODING=pcoem850. SAS IMPORT procedure reads and transcodes data from pcoem850 to Microsoft Windows default WLATIN1.

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

GETDELETED=YES | NO;

indicates whether to write records to the SAS data sets that are marked for deletion but have not been purged.

YES	writes records to the SAS data sets that are marked for deletion but have not been purged.
NO	does not write records to the SAS data sets that are marked for deletion but have not been purged.

Examples of Importing and Exporting DBF Files

Export Data to a DBF File from a SAS Data Set

The following example code 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 V9 "&sasdir";
PROC EXPORT DATA=SDF.EMPLOYEE (where=(HIREDATE is not missing))
            OUTFILE="&tmpdir.test.dbf"
            DBMS=DBF REPLACE; 
RUN;
```
Import Data from a DBF File into a SAS Data Set

The following example code 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;
```
Export Data to a DBF File from a SAS Data SAS Using Encoding

The following example code exports data to a DBF file named Oem850.dbf from a SAS data set named SDF.ORDERS. The example code 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;
```
Import and Translate Data from a DBF File

The following example code imports data from a DBF file named Oem850.dbf, which was saved with pcoem850 encoding. The codes read 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;
```

Top of Page