ACCESS Procedure: WKn Specifics

See the ACCESS procedure for PC files for general information about this feature. This is the WKn-specific syntax for the ACCESS procedure and the description of the ACCESS procedure data conversions.

ACCESS Procedure Syntax for WKn Files

To create an access descriptor, use the DBMS= WK n option and these database-description statements:

GETNAMES
PATH
RANGE
SCANTYPE
SKIPROWS
WORKSHEET

These database-description statements supply WKn-specific information to SAS and must immediately follow the CREATE or UPDATE statement that specifies the access descriptor to be created or updated. In addition to the database-description statements that you can use editing statements when you create an access descriptor. These editing statements must follow the database-description statements.

Database-description statements are required only when you create access descriptors. Because WKn information is stored in an access descriptor, you do not need to repeat this information when you create view descriptors.

The SAS/ACCESS interface to WKn uses the following procedure statements:

PROC ACCESS DBMS=WK1|WK3|WK4;
ASSIGN YES | NO | Y | N
CREATE <libref.member-name.>ACCESS | VIEW;
DROP <'column-identifier-1'>…<'column-identifier-n'>
FORMAT <'column-identifier-1'> <'SAS format-name-1'>...<'column-identifier-n'>
<'SAS format-name-n'>
GETNAMES YES | NO | Y | N
LIST ALL|VIEW |<'column-identifier'>
PATH='path-and-filename.WK1 | .WK3 | .WK4'| <filename>| <fileref>
RANGE <'range-name'>|<'range-address'>
SCANTYPE YES | NO | Y | N |
SKIPROWS <number-of-rows-to-skip>
UPDATE libref.member-name.ACCESS | VIEW
WORKSHEET <worksheet-letter>| worksheet-name
MIXED YES | NO | Y | N
RENAME <'column-identifier-1''SAS variable-name-1'>...
<'column-identifier-n''SAS variable-name-n'>
RESET ALL | 'column-identifier-1' …<'column-identifier-n'>;
SELECT ALL | <'column-identifier-1'> …<'column-identifier-n'>
SUBSET <selection-criteria>
TYPE <column-identifier-1> C |N …<'column-identifier-n'>C | N
UNIQUE =YES | NO | Y | N
RUN;

The QUIT statement is also available in the Access procedure. It causes the procedure to terminate. QUIT is used most often in interactive line and batch modes to exit the procedure without exiting SAS.

The following list provides detailed information about the WKn-specific statements:

GETNAMES=YES| NO |Y |N

determines whether SAS variable names are generated from column names in the first row of the Lotus range when an access descriptor is created. When you update a descriptor, you are not allowed to specify the GETNAMES statement.

The GETNAMES statement is optional. Omit the statement and the default value GETNAMES=NO is used. The SAS/ACCESS interface generates the SAS variable names VAR0, VAR1, VAR2, and so on. Specify GETNAMES=YES and the SAS variable names are generated from the column names in the first row of the Lotus range. GETNAMES=YES also sets the default value of SKIPROWS to 1.

You can change the default value from NO to YES by setting the SS_NAMES environment variable. For more information, see Setting Environment Variables for WKn Files.

The GETNAMES statement is a database-description statement. It must follow the CREATE statement and precede any editing statements when you create a descriptor.

RANGE ='range-name' |'range-address'

subsets a specified section of a WK n file worksheet. The range-name is the name that is assigned to a range address within the worksheet. Range names can be up to 15 characters long and are not case sensitive. Specify a range name, the name must have been previously defined in the WK n file. 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 WK n worksheet file. The beginning and ending cells are separated by two periods. The range address C9..F12 indicates a cell range that begins at Cell C9, ends at Cell F12, inclusive.

The RANGE statement is optional. Omit RANGE and the entire worksheet is accessed as the default range.

The RANGE is a database-description statement. It must follow the CREATE statement and precede any editing statements when you create a descriptor.

SCANTYPE= YES | NO | Y | N | number-of-rows

finds the most common Lotus 1-2-3 format for each column in a specified number of rows in a WKn worksheet to generate the SAS format. SAS variable formats are generated from the Lotus 1-2-3 formats found in the first row of the worksheet or in the specified range of the worksheet.

The SCANTYPE statement is optional, and its default value is NO. Specify YES and the ACCESS procedure scans the Lotus 1-2-3 formats of all the rows in each column of the range. The procedure uses the most common format to generate the default SAS format for each column. Specify a number of rows for the Access procedure to scan only the specified number of rows. The procedure returns the most common format.

Specify the SKIPROWS statement and the ACCESS procedure skips the specified rows. It starts scanning the Lotus 1-2-3 format from the next row. Specify SKIPROWS=3 and the Access procedure begins scanning the formats on the fourth row.

You can change the default value to YES by setting the SS_SCAN environment variable. For more information, see Setting Environment Variables for WKn Files.

Specifying SCANTYPE=0 is equivalent to specifying SCANTYPE=NO.

The SCANTYPE statement is a database-description statement. It must follow the CREATE statement and precede any editing statements when you create a descriptor.

SKIPROWS = number-of-rows-to-skip

specifies the number of rows, beginning at the top of the range in the WKn file, to ignore when you are reading data from the WKn file. The default value for SKIPROWS is 0. The skipped (or ignored) rows often contain information such as column labels or names, or underscores rather than input data.

If GETNAMES=YES, the default value of SKIPROWS= automatically changes to 1. The first row of data and formats after SKIPROWS in a range are used to generate the SAS variable types and formats. You can use the SCANTYPE= statement to scan the formats of specified rows. Then use the most common type and format to generate the SAS variable types and formats.

The SKIPROWS= statement is a database-description statement. It must follow the CREATE statement and precede any editing statements when you create a descriptor. The CREATE= option precedes the SKIPROWS= option. Editing statements follow the SKIPROWS= option.

WORKSHEET= worksheet-letter | worksheet-name

identifies a particular worksheet when you are reading from a WKn file that contains more than one worksheet. You can specify a worksheet name or a worksheet letter using the WORKSHEET statement. Worksheet 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. Specifying WORKSHEET=B identifies worksheet B from a group of worksheets.

The WORKSHEET statement is an optional database-description statement. It must follow the CREATE statement and precede any editing statements when you create an access descriptor.

ACCESS Procedure Data Conversions for WKn Files

Use the Access procedure to define descriptors that identify spreadsheet data and the conversions necessary to use that data in SAS programs.

The Lotus label data type is formatted as a SAS character type, and the Lotus 1-2-3 number data type is formatted as a SAS numeric type.

Fonts, attributes, and colors in the WKn files are not read into the SAS data sets. The ACCESS procedure supports most of the WKn number formats and automatically converts them to corresponding SAS formats.

Any WKn data strings longer than 200 characters are truncated while being converted into SAS data sets. Any SAS data set created from WKn files can contain up to 256 variables and 8,192 observations.

The following table shows the default SAS System variable formats that the ACCESS procedure assigns to each type of WKn file. Numeric data include date and time values.

Default SAS System Variable Formats for WKn File Data

WKn File Data				SAS Variable Format
Data Type	Data Format	Column Width	Decimal Number	Type	Format
Char**	*	w		Char	$w.
Numeric***	Default	w		Num	BESTw.
Numeric	AUTOMATIC	w	d	Num	BESTw.
Numeric	FIXED	w	d	Num	w.d
Numeric	SCIENTIFIC	w	d	Num	Ew.d
Numeric	CURRENCY	w	d	Num	DOLLARw.d
Numeric	PERCENT	w	d	Num	PERCENTw.d
Numeric	COMMA	w	d	Num	COMMAw.d
Numeric	GENERAL	w		Num	BESTw.
Numeric	DD-MON-YY	w.		Num	DATE7.
Numeric	DD-MON	w		Num	DATE7.
Numeric	MON-YY	w		Num	MONYY5.
Numeric	MM-DD-YY	w		Num	MMDDYY8.
Numeric	MM-DD	w		Num	MMDDYY8.
Numeric	HH-MM-SS	w		Num	TIME8.
Numeric	HH-MM-SS	w		Num	TIME5.
Numeric	HH-MM-SS AM/PM	w		Num	TIME12.
Numeric	HH-MM AM/PM	w		Num	TIME9.

* Any valid Lotus 1-2-3 data format.

** Label or formula string data.

*** Number or formula data.

If WKn file fall outside of the valid SAS data ranges, when you try to access the data, an error message is written to the SAS log.

The SAS/ACCESS interface does not fully support the Lotus 1–2–3 hidden and text formats. WKn data in hidden format are displayed in SAS data sets. You can drop the hidden column when creating the access descriptor.

To display the formula in the text format, add a label prefix character. This indicates that the formula entry is a label. Otherwise, the results of the formula are displayed.