Chapter Contents
Previous
Next
|
Chapter Contents |
Previous |
Next |
| SAS/ACCESS Software for PC File Formats: Reference |
WK1, WK3, and WK4 files contain data in the form of Lotus 1-2-3 spreadsheets. The SAS/ACCESS interface supports Releases 4 and 5 of the Lotus 1-2-3 WK4 file format. Unless otherwise noted, WK1, WK3, and WK4 files are referred to collectively throughout this report as WKn files, where n stands for 1, 3, or 4.
Note: The SAS/ACCESS interface does not support
the .123 format for files from Lotus SmartSuite 97 software. ![[cautionend]](../common/images/cautend.gif)
| WKn Files |
In both spreadsheets and database files, the data are 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. Columns and Rows of Data in a WKn File illustrates a portion of a standard 1-2-3 worksheet.
Columns and Rows of Data in a WKn File
![[IMAGE]](./images/01320017.gif)
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. The SAS/ACCESS interface to WKn files uses only one worsheet, however, and defaults to worksheet A.
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 not contain any spaces. For example, if the range B3..D6 is named GRADE_TABLE, then the formula @AVG(GRADE_TABLE) has the same value as @AVG(B3..D6) and identifies the data in the range.
For more information on ranges and their naming conventions,
see the documentation that accompanies your Lotus 1-2-3 software.
path= 'c:\sasdemo\library\new_customer_1999.wk4';). However, WKn files with long names might not be accepted by some versions of Lotus
1-2-3.
| WKn Data Types |
Character data are 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 quote 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 also can 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 73050. 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. For information on how the SAS/ACCESS interface handles date and time values and formats, see How the SAS System Handles Date and Time Values.
When you create an access descriptor, the interface software uses the column types and formats in the .WK n file to determine the corresponding SAS variable formats. The SAS System generates its default formats based on the values that you specify for the SCANTYPE, SKIPROWS, and GETNAMES statements. You can change the formats generated by the software interface. For more information, see How the SAS/ACCESS Interface to WKn Files Works.
When you browse a view descriptor, any data value that does not match the column type (character or numeric) specified in the descriptor is treated as a missing value. This is the default action. However, you can use the MIXED=YES statement to convert numeric data values in a character column to their character representation when you create an access descriptor.
You can also set the SS_MIXED environment variable to
YES
in your SAS configuration file so that both numeric and character data are
displayed as SAS character data. Add this line to your SAS configuration
file:
-SET SS_MIXED YES
See Setting Environment Variables for more information on environment variables. For more information on changing the column type, refer to the sections on WK-specific procedure statements later in this chapter.
| How the SAS System Handles Date and Time Values |
Lotus 1-2-3 date and time values and formats are described
in WKn Data Types.
The SAS System handles date and time values differently than Lotus. A SAS date value is an integer that represents the number of days between January 1, 1960 and a specified date. A SAS time value is an integer that represents the number of seconds since midnight of the current day. When a date and a time are both present, the SAS System stores the value as the number of seconds since midnight, January 1, 1960. For example, for 1:00 p.m., March 12, 1994, the SAS date value is 12489, and the SAS time value is 46800. Therefore, the SAS datetime value is 1079096400. (footnote 1)
To convert a Lotus 1-2-3 datetime format to a SAS datetime format, you need a SAS datetime format in the view descriptor. For example, changing the default SAS numeric format (15.2) to a SAS date format in the descriptor causes the Lotus 1-2-3 date value (based on January 1, 1900) to be converted to an equivalent SAS date value (based on January 1, 1960). In other words, the Lotus 1-2-3 numeric value for January 1, 1960 (which is 21916) is converted to the equivalent SAS representation of January 1, 1960 (which is 0) only if a SAS datetime format is assigned in the descriptor for that column. Otherwise, the Lotus 1-2-3 value of 21916 is treated as a SAS numeric value of 21916.
The table below shows how the SAS System uses a Lotus 1-2-3 internal datetime value to convert to a SAS internal datetime value.
| For a SAS format | SAS System uses |
|---|---|
| date | if the Lotus datetime value is less than 60: integer portion of the Lotus 1-2-3 datetime value - 21915 |
| if the Lotus datetime value is greater than 60: integer portion of the Lotus 1-2-3 datetime value - 21916 | |
| time | decimal portion of the Lotus 1-2-3 datetime value * 86400 |
| date-and-time | if the Lotus datetime value is less than 60: (integer and decimal portion of the Lotus 1-2-3 datetime value - 21915) * 86400 |
| if the Lotus datetime value is greater than 60: (integer and decimal portion of the Lotus 1-2-3 datetime value - 21916) * 86400 |
However, if a SAS datetime format is not specified in the input SAS data set, you have to assign a format by using a PROC DBLOAD FORMAT statement. Doing so assigns a Lotus 1-2-3 datetime format to the SAS variable when the variable is loaded into a WKn file. If you do not assign a SAS datetime format, the SAS numeric-datetime value is written to the WKn file. Because SAS dates are based on January 1, 1960, and Lotus 1-2-3 dates are based on January 1, 1900, the datetime value in the WKn file will be inaccurate.
To maintain a SAS variable format in the input data set, yet change it only while the DBLOAD procedure is in progress, use the FORMAT statement in PROC DBLOAD. This statement enables you to assign a temporary format to a SAS variable for the duration of the procedure without affecting the input SAS data set.
For example, if the SAS format for the BIRTHDAT variable in the MYDATA.SASEMPS access descriptor is left at the default 15.2 format, you can specify the FORMAT statement in the PROC DBLOAD statement. This specification changes the variable's format to DATE7. while you are creating and loading the WKn file. When you load the WKn file, the DATE7. format becomes an equivalent Lotus column format, DD-MON-YY. When the DBLOAD procedure has completed, the SAS format for the BIRTHDAT variable returns to the 15.2 format.
You can specify the FORMAT statement when you invoke the DBLOAD procedure to assign a temporary format to the variables in your input SAS data set. For more information, see the description of the FORMAT statement in DBLOAD Procedure Statements for WKn Files.
| ACCESS Procedure Data Conversions |
Fonts, attributes, and colors in the WKn files are not read into the SAS data sets. However, the ACCESS procedure supports most of the WKn number formats and automatically converts them to the corresponding SAS formats. Any WKn data strings longer than 200 characters are truncated while being converted into SAS data sets, and any SAS data file created from WKn files can only contain up to 256 variables and 8,192 observations.
Converting SAS System Variable Formats to WKn File Data shows the default SAS System variable formats that the ACCESS procedure assigns to each type of WKn file data. WKn file numeric data include date and time values. See How the SAS System Handles Date and Time Values for more information.
If WKn file data fall outside of the valid SAS data ranges, you receive an error message in the SAS log when you try to access the data.
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; however, you can drop the hidden column when you are creating the access descriptor. If you want to display the formula in the text format, add a label prefix character to indicate that the formula entry is a label. Otherwise, the results of the formula are displayed.
If you have set the SS_MIXED environment variable to
YES,
the numerical values in WKn files are converted
to character strings in SAS data sets if the corresponding SAS variable type
is specified as character.
| DBLOAD Procedure Data Conversions |
The SAS/ACCESS interface automatically converts SAS formats to the same or associated Lotus 1-2-3 formats and column widths. However, you can temporarily assign other formats and column widths to SAS variables by using the FORMAT statement. Converting SAS System Variable Formats to WKn File Data shows the SAS System variable types and formats and the WKn data types, formats, and column widths that you can assign them to.
Note: The FORMAT statement in PROC DBLOAD only changes the format of SAS variables
while you are creating and loading the WKn files.
When the procedure is completed, the formats of SAS variables return to their
original settings.
WKn date and time values are numeric data. See How the SAS System Handles Date and Time Values for more information.
| 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 | |
![[arrow]](../common/images/fntarrow.gif)
|
Chapter Contents |
Previous |
Next |
 Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.