ACCESS Procedure: DIF Specifics

See ACCESS Procedure Data Conversions for DIF Files.

ACCESS Procedure Syntax for DIF Files

To create an access descriptor, use the DBMS=DIF option and the database-description statements PATH=, DIFLABEL=, and SKIPROWS=. These statements supply DIF-specific information to SAS, and must immediately follow the CREATE statement. In addition to the database-description statements, you can use optional editing statements when you create an access descriptor. These editing statements must follow the database-description statements.

Database-description statements are required only when you create access descriptors. Because the DIF information is stored in an access descriptor, you do not need to repeat this information when you create view descriptors.

PROC ACCESS <option(s)>;

CREATE <libref.>member-name.ACCESS | VIEW

The SAS/ACCESS interface to DIF uses the following procedure statements:

ASSIGN= | AN YES | NO
DIFLABEL
DROP 'column-identifier-1'... 'column-identifier-n'
FORMAT 'column-identifier-1' 'SAS format-name-1'...SAS variable-name-n 'column-identifier-n'
LIST= ALL | VIEW | 'column-identifier'
PATH='path-and-filename'|.DIF|filename fileref
RENAME 'column-identifier-1' SAS variable-name-1...'column-identifier-n' SAS variable-name-n
RESET= ALL |' column-identifier-1'...'column-identifier-1
SELECT=ALL|'column-identifier-1'...'column-identifier-n'
SKIPROWS='number-of-rows-to-skip'
SUBSET
TYPE='column-identifier-1'= C | N ...'column-identifier-n'= C | N
UPDATE='path-and-filename'| 'filref'
UNIQUE=YES | NO
RUN;

The QUIT statement is also available in the Access procedure. It causes the procedure to terminate. QUIT is used most often in interactive line mode and batch mode to exit the procedure without exiting SAS.

The following list provides detailed information about the DIF-specific statements:

DIFLABEL

indicates whether variable names are generated from the first row of the columns. Omit this statement and variable names are generated based on the columns' placement in the first row. SAS labels each column as COL0, COL1, COL2, and so on. These labels are the names of SAS variables in the access descriptor.

Specify DIFLABEL and the ACCESS procedure reads column labels from the first row of the DIF file. The labels are also used as the SAS variable names in the access descriptor. You provide the DIF file column labels; they are not the letters ( A, B, and so on) that identify the columns in a worksheet. Specify DIFLABEL , and the SKIPROWS= statement automatically changes to 1.

Always specify the DIFLABEL statement after the PATH= statement and before any editing statements. When you update a descriptor, the DIFLABEL statement cannot be changed.

Create an Access Descriptor and a View Descriptor Based on DIF file data.

OPTIONS LINESIZE=80;
LIBNAME difliba ’SAS data-library’;
LIBNAME diflibv ’SAS data-library’;

PROC ACCESS DBMS=DIF;
/* create access descriptor */
CREATE difliba.custs.access;
		PATH=’c:\difiles\dbcusts.dif’;
		DIFLABEL;
		SKIPROWS=2;
		ASSIGN=yes;
		RENAME customer = custnum;
		FORMAT firstorder DATE9.;
		LOST all;
/* create usacust view */
		CREATE diflibv.usacust.view;
		SELECT customer state zipcode name
			firstorder;
RUN;

SKIPROWS =number-of-rows-to-skip;

specifies the number of rows, beginning at the top of the DIF file, to skip when reading data. The default value for SKIPROWS is 0. The skipped rows often contain information such as column labels or names, or underscores rather than input data.

When you specify the DIFLABEL statement, the default value of SKIPROWS automatically changes to 1.

When you are creating a description, SKIPROWS= should always follow the PATH= statement and precede any editing statements. The first row of data after SKIPROWS= is used to generate the SAS variable types and formats. If there is no data in the first row of a column after SKIPROWS, the data in the rest of the column is assumed to be character. This is true even if the data in the next row is numeric.

Any data value in a column that does not match the type is treated as a missing value.

If you set the DIFNUMS environment variable to YES in your SAS configuration file, numeric values in a character column are converted to character. The values are not treated as missing values. To set the DIFNUMS value, add the following line to your SAS configuration file:

-SET
                                 DIFNUMS YES

The default value for the DIFNUMS environment variable is NO.

You can change the column type from the default type that the Access procedure defines when you create an access descriptor.

Note: Refer to the SAS documentation for your operating environment for more information about environment variables.

ACCESS Procedure Data Conversions for DIF Files

The following table shows the default SAS variable formats that the ACCESS procedure assigns to each type of DIF file data. DIF file numeric data includes date and time values.

Default SAS Variable Formats for DIF File Data

DIF File Data	SAS Variable Format
C (Character)	$20.
N (Numeric)	15.2

If DIF file data falls outside of the valid SAS data ranges, you get an error message in the SAS log when you try to read the data.

Datetime Conversions in the ACCESS Procedure

When you create an access descriptor, SAS cannot distinguish a Lotus datetime value from other numeric data. SAS stores the Lotus datetime value as a number and displays it using the SAS variable format 15.2 (the default format for this interface).

To convert a Lotus datetime value to a SAS datetime value, you must specify a SAS datetime format in the access descriptor. A Lotus datetime value is a number that represents the number of days between January 1, 1900, and a specified date. Changing the default SAS format (15.2) to a datetime format in the descriptor causes the Lotus value to be converted to an equivalent SAS datetime value based on January 1, 1960.

If a SAS datetime format is stored in a column's descriptor, the Lotus value for January 1,1960 (21,916) is converted to the SAS value for January 1,1960 (0). Otherwise, the Lotus value of 21,916 is treated as a SAS numeric value of 21,916.

Value-to-Format Conversions

For a SAS format	SAS uses
date	integer portion of the Lotus number
time	decimal portion of the Lotus number
date-and-time	integer and decimal portion of the Lotus number