File Format-Specific Reference for the IMPORT and EXPORT Procedures

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

Columns and Rows of Data in a WKn File

[Columns & Rows of Data in a WKn File]

Column letters for each column appear above the worksheet. 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 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. 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	In ROC EXPORT
WK4 WK3	GETNAMES	Yes\|No	Yes	Yes	No
	RANGE	Range Name\|Absolute Range Value		Yes	No
	SHEET	Sheet name		Yes	Yes
WK1		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.

YES	specifies that the IMPORT procedure generate SAS variable names from the data values in the first row of the imported Excel file.
NO	specifies that the IMPORT procedure generate SAS variable names as F1, F2, F3, and so on.

Default:	Yes
Restriction:	Valid only for Microsoft Windows.
Restriction:	Valid only for the IMPORT procedure.
Restriction:	Supported only when DBMS=EXCEL.
Restriction:	If data in the first row 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 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. The range- name is a user- defined spreadsheet name, that represents a range of cells within the spreadsheet in the Excel file. The range name is not case sensitive and does not allow any special character (except an underscore). The range-address is identified by the top left cell that begins the range and the bottom right cell that ends the range within the Excel worksheet file. The beginning and ending cells are separated by two periods. For example, the range address C9..F12 indicates a cell range that begins at Cell C9, ends at Cell F12, and includes all cells in between. You must define range name with a workbook scope so that the name is visible to SAS.

When data is imported from an Excel file, a sheet name that is appended with a $ character is treated as a range name. The range name refers to the whole sheet.

You can use the DATASETS procedure to list the data set names, that are mapped to the range names. If the displayed range name contains single quotes, keep the single quotes as part of the range name to access the sheet. For example, to retrieve data from the spreadsheet for two separate sheet names, My#Test and CustomerOrders:

RANGE="'My#Test$'";
RANGE="' CustomerOrders$'";

Absolute range identifies the top left cell that begins the range and the bottom right cell that ends the range.

Example 1. C2:F12 represents cells within Column C, Row 2, and Column F, Row 12. Here is another example:

RANGE="'summary$a4:b20'";

Example 2. $ indicates the end of the sheet name, colon (:) separates the values for upper left (UL) and lower right (LR) of the cell range. If this statement is not specified, the IMPORT procedure reads the entire spreadsheet as a range.

If the range name is available, it is recommended that you use RANGE= option without the SHEET= option for the IMPORT procedure. To use the absolute range address, it is strongly recommended that you use the full range address with quotes. For example, specify 'sheet_name$A1:C7'n .

Restriction:

Supported only for the IMPORT procedure.

SHEET= worksheet-letter| 'worksheet-name'; (valid for only the IMPORT procedure)

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 - worksheet Z and worksheet AA - 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, 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.

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;

Top of Page