DBLOAD Procedure: DIF Specifics

See The DBLOAD Procedure for general information about this feature. This section provides DIF-specific syntax for the DBLOAD procedure and describes DBLOAD procedure datetime conversions. See Datetime Conversions in the DBLOAD Procedure.

DBLOAD Procedure Syntax for DIF Files

To create and load a DIF table, SAS/ACCESS Interface to PC Files uses the following statements.

PROC DBLOAD DBMS=DIF
DATA=<libref.SAS data-set>
PATH=<'path-and-filename'>.DIF|<filename>| fileref
DIFLABEL
ACCDESC=<libref.>access-descriptor
DELETE variable-identifier-1 <…variable-identifier-n>
ERRLIMIT=error-limit;
FORMAT SAS variable-name-1 SAS format-1 <SAS variable-name-n SAS format-n>
LABEL
LIMIT=load-limit
LIST ALL | COLUMNS | FIELDS | <variable-identifier>
LOAD
RENAME <variable-identifier-1>= <column-name-1>…
<variable-identifier-n>=<column-name-n>'
RESET ALL | <variable-identifier-1> …<variable-identifier-n>
WHERE <SAS where-expression>
RUN;

The QUIT statement is also available in the DBLOAD 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

writes column labels to the first row of the new DIF file and follows the column labels with a blank row. The column labels can be default SAS variable names. Specify the LABEL statement to use SAS labels. You can modify the column labels using the RENAME statement.

Note: If this statement is omitted, data is read from the data set and written to the DIF file beginning in the first row. No column labels are written to the file.

FORMAT SAS variable-name-1SAS format-1…SAS variable-name-nSAS format-n

assigns a temporary SAS format to a SAS variable in the input SAS data set. This format temporarily overrides any other format for the variable. The assignment lasts for the duration of the procedure. You can assign formats to as many variables as you want in one FORMAT statement.

Use the FORMAT statement to change the format, column width, or the number of decimal digits for columns being loaded into the PC file. If you change the SAS variable format 12.1 to DOLLAR15.2, the column format of the loaded data changes. The fixed numeric format with a column width of 12 and one decimal digit changes to a currency format with a column width of 15 and two decimal digits.

This example creates a DIF table, Exchange.dif, from the data file Dlib.RateOfex. An access descriptor AdLib.Exchange is also created based on the new DIF table. You must be granted the appropriate privileges in order to create new DIF files.

LIBNAME dlib 'SAS data-library';
LIBNAME adlib 'SAS data-library';

PROC DBLOAD DBMS=DIF DATA=dlib.rateofex;
  ACCDESC=adlib.exchange;
   PATH='c:\difiles\sasdemo\exchange.dif';
   DIFLABEL;
   RENAME fgnindol=fgnindolar 4=dolrsinfgn;
   LOAD;
RUN;

Datetime Conversions in the DBLOAD Procedure

If a SAS variable is specified with a date, time, or datetime format in the FORMAT statement, the interface view engine converts that value into the equivalent Lotus datetime value. The conversion is written to the DIF file when it is created. The DIF file has no way of relating this formatting information to Lotus products. Therefore, when you load the DIF file into a Lotus 1-2-3 worksheet, the datetime values are represented as numbers. It is recommended that you assign (from within Lotus) a Lotus datetime format to any datetime column that you load from a DIF file.

If a SAS variable represents a date, time, or datetime value, but it has not been assigned a SAS datetime format, the SAS datetime value is represented as a number. The number is not converted into an equivalent Lotus datetime value in the DIF file. Rather, the number is written to the new DIF file as is.

Note: SAS dates are based on January 1, 1960. Lotus dates are based on January 1, 1900. If you assign a Lotus datetime format to an unconverted Lotus column, the datetime values in that column are inaccurate.

Use the DBLOAD FORMAT statement to maintain a SAS variable format in the input data set. This changes the format only while the DBLOAD procedure is in progress. Assigning a temporary format to a SAS variable does not affect how SAS stores the variable.

If the SAS format for the BirthDat variable in the MyData.SasEmps data set is the default 15.2 format, you can specify a FORMAT statement to change the format to DATE7. Use the FORMAT statement while you are creating and loading the DIF file. When you load the DIF file into a Lotus 1-2-3 worksheet, specify an equivalent Lotus date format. Specify the FORMAT statement when you invoke the DBLOAD procedure using any of the methods of processing. When the DBLOAD procedure has completed, the SAS format for the BirthDat variable reverts to its original 15.2 format.

Note: There are certain display restrictions on the SAS datetime values that are loaded into Lotus 1-2-3 worksheets through DIF files. If you load a SAS variable with a DATETIMEw.d format into a DIF file, Lotus stores the number with both the integer and decimal. When you load the DIF file into a Lotus 1-2-3 worksheet that you can specify a date format for the column. DATE formats only use the integer portion of the data. Alternatively, you can also specify a TIME format that only uses the decimal portion of the data. You cannot specify both at the same time.

Setting Environment Variables for DIF File Data Types

By default, 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, any numeric data values in a character column are converted to the character representation of the number. They are not treated as missing values. Add the following line to your SAS configuration file to set the DIFNUMS environment variable to YES:

-SET DIFNUMS YES

The default for the DIFNUMS environment variable is NO. Refer to the SAS Companion for your operating system for more information about environment variables.

You can change the column type from the type that SAS/ACCESS determines when you create an access descriptor.