DBLOAD Procedure: WKn Specifics

See The DBLOAD Procedure for general information about this feature. This is WKn-specific syntax for the DBLOAD procedure and description of DBLOAD procedure data conversions. See DBLOAD Procedure Data Conversions for WKn Files.

DBLOAD Procedure Syntax for WKn Files

To create and load a WKn table, the SAS/ACCESS interface to WKn uses the following statements:
PROC DBLOAD DBMS= WK1 | WK3 | WK4
DATA= <libref.SAS data-set>
PATH=<'path-and-filename'>.WK1|.WK3| .WK4|<'filename'>| <fileref>
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
PUTNAMES=YES | NO | Y | N
RENAME <'variable-identifier-1'>= <'column-name-1'>...<'variable-identifier-n'> =
<'column-name-n'>
RESET ALL |<'variable-identifier-1'> <'variable-identifier-n'>... <'column-identifier-n'> C | 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.
WKn-specific statements are as follows:
PUTNAMES =YES|NO|Y|N
writes column names to the first row of the new WKn file. The column names are the SAS variable names. You can use the LABEL statement to assign SAS variable labels. You can modify the column names using the RENAME statement.
The PUTNAMES statement is optional. Omit PUTNAMES= and data is read from the data set and written to the WKn file. Data is written beginning in the first row of the WKn file. No column names are written to the file.
FORMAT SAS variable-name-1 SAS format-1 <SAS variable-name-n SAS format-n>
assigns a temporary format to a SAS variable in the input SAS data set. This format temporarily overrides any other format for the variable. The assignment lasts only for the duration of the procedure. 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.

DBLOAD Procedure Data Conversions for WKn Files

SAS data is read into Lotus 1-2-3 data when a table is loaded. In this conversion, SAS character data type is converted into the Lotus 1-2-3 label type. SAS numeric type is converted into the Lotus 1-2-3 number type.
The SAS/ACCESS interface converts SAS formats to the same or associated Lotus 1-2-3 formats and column widths. You can temporarily assign other formats and column widths to SAS variables with the FORMAT statement.
Note: The FORMAT statement in the DBLOAD procedure changes only the format of SAS variables while you are creating and loading the WKn files. When the procedure finishes, the formats of the SAS variables return to their original settings.
Converting SAS Variable Formats to WKn File Data
SAS Variable Format
WKn File Data
Type
Data Format
Data Type
Column Format
Column Width
Number
Char
$w.
LABEL
DEFAULT
w
Char
$CHARw.
LABEL
DEFAULT
w
Num
w.d
NUMBER
FIXED
w
d
Num
Fw.d
NUMBER
FIXED
w
d
Num
Ew.d
NUMBER
SCIENTIFIC
w
d
Num
DOLLARw.d
NUMBER
CURRENCY
w
d
Num
PERCENTw.d
NUMBER
PERCENT
w
d
Num
COMMAw.d
NUMBER
COMMA
w
d
Num
BESTw.
NUMBER
DEFAULT
w
Num
BESTw.
NUMBER
GENERAL
w
Num
DATE5.
NUMBER
DD-MON
7
Num
DATE7.
NUMBER
DD-MON-YY
10
Num
MONYY5.
NUMBER
MON-YY
7
Num
MMDDYY5.
NUMBER
MM-DD
6
Num
MMDDYY8.
NUMBER
MM-DD-YY
9
Num
TIME5.
NUMBER
HH-MM-SS
6
Num
TIME8.
NUMBER
HH-MM-SS
9
Num
TIME9.
NUMBER
HH-MM AM/PM
9
Num
TIME12.
NUMBER
HH-MM-SS AM/PM
12

Setting Environment Variables for WKn Files

You can change the default behavior of the SAS/ACCESS interface by setting environment variables in your SAS configuration file. You can set four SAS/ACCESS environment variables: SS_MISS NULLS, SS_MIXED, SS_NAMES, and SS_SCAN. Setting these variables in your SAS configuration file changes how the interface works by default.
The configuration file omits these three environment variables, which means their default values are NO.
SS_MISS NULLS
The DBLOAD procedure loads Lotus @NA cell values for missing values. Use this option to specify a null cell value instead. If set, missing values in a SAS data set
are displayed as blanks in the Lotus 1-2-3 table.
SS_MIXED= YES | NO
YES allows both Lotus 1-2-3 numeric and character data in a column to be displayed as SAS character data. The Lotus 1-2-3 numeric data is converted to its character representation when its corresponding SAS variable type is defined as character.
NO does not convert Lotus 1-2-3 numeric data in a column into SAS character data. Lotus 1-2-3 numeric data is read in as SAS missing values when its corresponding SAS variable type is defined as character. NO is the default.
Setting the SS_MIXED environment variable changes the default value of the MIXED statement in the Access procedure.
SS_NAMES YES | NO
YES in the Access procedure generates SAS variable names from column names in the first row of the worksheet or specified range of the worksheet. Data is then read starting from the second row.
YES in the DBLOAD procedure writes column names using SAS variable names or SAS variable labels to the first row of the new WKn file. Data is then read from the data set and written to the WKn file beginning with the second row.
NO in the Access procedure generates the SAS variable names VAR0, VAR1, VAR2, and so on. It reads data from the first row of the worksheet or a specified range. NO is the default.
NO in the DBLOAD procedure reads the data from the data set and writes it to the WKn file beginning with the first row.
Setting the SS_NAMES environment variable changes the default value of the GETNAMES= option in the Access procedure, and the PUTNAMES= option in the DBLOAD procedure.
SS_SCAN= YES | NO |number-of-rows
YES scans the data type and format of rows in a worksheet or specified range. If SKIPROWS= is specified, the first row is after skipping the number of rows specified. SS_SCAN finds the most common Lotus 1-2-3 data type and format to generate the SAS data type and format. If a number of rows is specified, the Access procedure scans only the data type and format from the specified rows.
NO uses the type and format of the first row in a worksheet or specified range. If SKIPROWS= is specified, the first row is after skipping the number of rows specified. NO is the default.
Number-of-rows scans only the type and format of the specified number of rows. Setting the number of rows is more efficient because data is read from only the specified number of rows rather than the entire file.
Setting the SS_SCAN environment variable changes the default value of the SCANTYPE= statement option in the Access procedure.
Copyright © SAS Institute Inc. All rights reserved.