Lotus 1-2-3 WKn Files :: SAS/ACCESS(R) 9.3 Interface to PC Files: Reference

WKn Files Essentials

This section introduces Lotus 1-2-3 WKn files. It focuses on the terms and concepts that help you use SAS/ACCESS Interface to PC Files.

SAS/ACCESS Interface to PC Files works with WK1, WK3, and WK4 (releases 4 and 5) files. These files contain data in the form of Lotus 1-2-3 spreadsheets. They are referred to collectively in this document as WKn files, where n represents releases 1, 3, or 4. SAS/ACCESS Interface to PC Files does not support the .123 format for files from Lotus SmartSuite 97 software.

Various software products, such as the Lotus 1-2-3 spreadsheet and database system, enable you to use spreadsheet or database files to enter, organize, and perform calculations on data. Spreadsheets are most often used for general ledgers, income statements, and other types of financial record keeping. Database files also enable you to organize related information, such as the data in an accounts-receivable journal.

In both spreadsheets and database files, the data is organized according to certain relationships among data items. These relationships are expressed in a tabular form, in columns and rows. Each column represents one category of data, and each row can hold one data value for each column.

A Lotus 1-2-3 spreadsheet is an electronic spreadsheet consisting of a grid of 256 columns and 8,192 rows. The intersection of a column and a row is called a cell. This display illustrates a portion of a standard 1-2-3 spreadsheet.

Columns and Rows of Data in a WKn File

Column letters for each column appear above the spreadsheet. Columns are lettered A–IV (A to Z, AA to AZ, BA to BZ, and so on, to IV). Row numbers for each row appear to the left of the spreadsheet. Rows are numbered 1 to 8,192. For WK1 files, only one spreadsheet (spreadsheet A) is allowed per file. For WK3 and WK4 files, up to 256 spreadsheets (spreadsheets A-IV) are allowed. However, the SAS/ACCESS interface to WKn files uses only one spreadsheet and defaults to spreadsheet A.

A range is a subset of cells in a spreadsheet. A range is identified by its address. The address begins with the name of the top left cell. The range ends with the name of the bottom right cell. The names are separated by two periods. For example, the range B2..E8 is the range address for a rectangular block of 28 cells whose top left cell is B2 and whose bottom right cell is E8 (as shaded in the figure).

You can give a name to a range and use the name in commands and formulas instead of the range address in Lotus 1-2-3. A range name can be up to 15 characters long and should contain no spaces. For example, if the range B3..D6 is named GRADE_TABLE, the formula @AVG(GRADE_TABLE) has the same value as @AVG(B3..D6).

For more information about ranges and their naming conventions, see the Lotus 1-2-3 software documentation.

WKn Data Types

Lotus 1-2-3 software has two data types: character and numeric. Lotus 1-2-3 character data can be entered as labels or formula string. Lotus 1-2-3 numeric data can be entered as numbers or formulas.

Character data is generally considered text and can include dates and numbers if prefixes are used to indicate character data and to align the data in the cell. For example, in Lotus 1-2-3, the value "110 Maple Street uses the double quotation mark prefix and aligns the label on the right side of the cell.

Numeric data can include numbers (0 through 9), formulas, and cell entries that begin with one of these symbols: +, $, @, -, or #.

Numeric data can also include date and time values. In Lotus 1-2-3 software, a date value is the integer portion of a number that can range from 01 January 1900 to 31 December 2099, that is, 1 to 73,050. A Lotus 1-2-3 software time value is the decimal portion of a number that represents time as a proportion of a day. For example, 0.0 is midnight, 0.5 is noon, and 0.999988 is 23:59:59 (on a 24-hour clock).

While a number can have both a date and a time portion, the formats in Lotus 1-2-3 display a number only in a date format or a time format. The conversion of date and time values between SAS data sets and Lotus 1-2-3 spreadsheets is transparent to users. However, you are encouraged to understand the differences between them.

Supported Import and Export Methods and Statements for WKn Files

The IMPORT | EXPORT method for WKn files uses ACCESS and DBLOAD procedures behind the scenes to access data in WKn files. This method is available only in Microsoft Windows.

Because the ACCESS and DBLOAD procedures are compatible only with SAS 6 procedures, SAS system options such as the VALIDVARNAME= option are ignored. This method has other SAS 6 limitations such as:

not being case sensitive
a maximum length of 8 bytes of SAS variable name
maximum length of 200 characters of data values

The following table lists the statements that are available to import and export data from Lotus 1-2-3 files using the IMPORT and EXPORT procedures on Microsoft Windows.

Import or Export Data from Lotus 1–2–3 Files

Data Source	Syntax	Valid Value	Default Value	PROC IMPORT	PROC EXPORT
WK4 WK3	GETNAMES	Yes \| No	Yes	Yes	No
	RANGE	Range Name \| Absolute Range Value		Yes	No
	SHEET	Sheet name		Yes	Yes
WK1	GETNAMES	Yes \| No	Yes	Yes	No
WK1	RANGE	Range Name \| Absolute Range Value		Yes	No

GETNAMES= YES | NO: specifies whether the IMPORT procedure is to generate SAS variable names from the data values in the first row of the import file.

If data in the first record of the input file contains special characters for a SAS variable name (such as a blank), SAS converts the character to an underscore. For example, the data value MY ID becomes the SAS variable name MY_ID.

YES specifies that the IMPORT procedure generate SAS variable names from the data values in the first record of the imported Excel file.

NO specifies that the IMPORT procedure generate SAS variable names as F1, F2, F3, and so on.

Default:YES

Restrictions:Valid only for DBMS=EXCEL.

Valid only for the IMPORT procedure.

When SAS reads the data value in the first row of the input file, SAS checks for invalid SAS name characters (such as a blank). Invalid characters are converted to an underscore. For example, the data value Occupancy Code becomes the SAS variable name Occupancy_Code.

RANGE= “ range-name” | “ absolute-range”: subsets a spreadsheet by identifying the rectangular set of cells to import from the specified spreadsheet. Range names can be up to 15 characters long and are not case sensitive. If you specify a range name, the name must have been previously defined in the WKn file. Absolute range identifies the top left cell that begins the range and the bottom right cell that ends the range. For example, `C2..F12' represents cells within column C, row 2 and column F, row 12. If this statement is not specified, PROC IMPORT reads the entire spreadsheet as a range.

Restriction: This statement is valid for PROC IMPORT only.

SHEET= worksheet-letter | 'worksheet-name': identifies a particular spreadsheet in a WKn file. Sheet names can be up to 15 characters long and are not case sensitive. A spreadsheet letter is a one- or two-letter alpha character. For WK1 files, there is only one spreadsheet letter: spreadsheet A. For WK3 and WK4 files, there can be up to 256 different spreadsheet letters: spreadsheet A - spreadsheet Z and spreadsheet AA - spreadsheet IV. The default value is A. For example, specifying SHEET=B; identifies spreadsheet B from a group of spreadsheets.

If this statement is not specified, the IMPORT procedure reads the first spreadsheet physically saved in the WKn file. To be certain that IMPORT procedure reads the desired spreadsheet, you should identify the spreadsheet by specifying SHEET= option.

Restriction: Valid for only the IMPORT procedure.

Example 1: Export a SAS Data Set to a WK4 File

This example exports data from SAS data set SDF.EMPLOYEE to a WK4 file Employee.wk4 without variables FRSTNAME and MIDNAME.

LIBNAME SDF "&sasdir";
PROC EXPORT DATA=SDF.EMPLOYEE(DROP=FRSTNAME MIDNAME)
            OUTFILE="&tmpdir.Employee.wk4"
            DBMS=WK4 REPLACE;
RUN;

Example 2: Import Data from a SAS Data Set from a WK4 File

This example imports data from a WK4 file named invoice.wk4 into SAS data set named Invoice. It retrieves data from Sheet A, within range from left top cell, A1, to right bottom cell, D12. It then reads the first row of data in the range as SAS variable names.

PROC IMPORT OUT=WORK.Invoice
            DATAFILE="&wkndir.Invoice.wk4"
            DBMS=WK4 REPLACE;
   SHEET='A';
   RANGE='A1..D12';
   GETNAMES=YES;
RUN;

Example 3: Export Data to a WK1 File from a SAS Data Set

The next example exports data to a WK1 file named Orders.wk1 from a SAS data set named SDF.ORDERS, without the variable SPECINST.

LIBNAME SDF   "&sasdir";
PROC EXPORT DATA=SDF.ORDERS(DROP=SPECINST)
            OUTFILE="&tmpdir.Orders.wk1"
            DBMS=WK1 REPLACE;
RUN;

Example 4: Import Data from a WK1 File into a SAS Data Set

This example imports data from a WK1 file named Orders.wk1 into a SAS data set named Test. It retrieves data from left top cell, B5, to right bottom cell, D12. SAS assigns name for each variable as VAR0, VAR1, and VAR2.

PROC IMPORT OUT=WORK.Test
            DATAFILE="&wkndir.orders.wk1"
            DBMS=WK1 REPLACE;
   RANGE='B5..D12';
   GETNAMES=NO;
RUN;