Lotus 1-2-3 WKn Files

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 and 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 worksheet 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. The following display illustrates a portion of a standard 1-2-3 worksheet.

Columns and Rows of Data in a WKn File

Column letters for each column appear above the worksheet. Columns are lettered A through 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 worksheet. Rows are numbered 1 to 8,192. For WK1 files, only one worksheet (worksheet A) is allowed per file. For WK3 and WK4 files, up to 256 worksheets (worksheets A-IV) are allowed. However, the SAS/ACCESS interface to WKn files uses only one worksheet and defaults to worksheet A.

A range is a subset of cells in a worksheet. A range is identified by its address, which begins with the name of the top-left cell and ends with the name of the bottom-right cell 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.

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 the following 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.

The import/export component for WKn files uses ACCESS and DBLOAD procedures behind the scenes to access data in WKn files. This component is available only on Microsoft Windows.

Note:   Because ACCESS and DBLOAD procedures are compatible only with Version 6 procedures, SAS system options such as VALIDVARNAME= are ignored. This component has other Version 6 limitations such as not being case sensitive, a maximum length of eight bytes of SAS variable name, and a maximum length of 200 characters of data value.   

The following tables list the statements that are available to import data from Lotus 1-2-3 files using the IMPORT and EXPORT procedures on Microsoft Windows. See Examples of Importing and Exporting WKn Files.

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 worksheet letter is a one- or two-letter alpha character. For WK1 files, there is only one worksheet letter: worksheet A. For WK3 and WK4 files, there can be up to 256 different worksheet letters: worksheet A through worksheet Z and worksheet AA through worksheet IV. The default value is A. For example, specifying WORKSHEET=B identifies worksheet B from a group of worksheets.

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

RANGE="range-name" | "absolute-range";

subsets a spreadsheet by identifying the rectangular set of cells to import from the specified spreadsheet. The 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. This statement is not valid for PROC EXPORT.

GETNAMES=YES | NO; (available only for Microsoft Windows)

determines whether to generate SAS variable names from the data values in the first record of the imported Excel file.

If a data value in the first record for the input file is read and it contains special characters that are not valid in a SAS name, such as a blank, SAS converts the character to an underscore. For example, the column name 'My ID' becomes the SAS variable name 'MY_ID' .

This statement is not valid for PROC EXPORT.

YES

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

NO

specifies that PROC IMPORT generate SAS variable names as VAR0, VAR1, VAR2, and so on.

1. Export a SAS Data Set to a WK4 File

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

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

2. Import Data from a SAS Data Set from a WK4 File

   The following example code 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;

3. Export Data to a WK1 File from a SAS Data Set

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

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

4. Import Data from a WK1 File into a SAS Data Set

   The following example code 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;

Copyright © 2007 by SAS Institute Inc., Cary, NC, USA. All rights reserved.