 |
|
| File-Specific Reference for the ACCESS and DBLOAD Procedures |
DBLOAD Procedure: WKn Specifics
See The DBLOAD Procedure for general information about this feature. This is WKn specific syntax for the DBLOAD procedure and description of DBLOAD procedure data conversions. See DBLOAD Procedure Data Conversions for WKn Files.
| DBLOAD Procedure Syntax for WKn Files |
To create and load a WKn table, the SAS/ACCESS interface to WKn uses the following statements:
|
PROC DBLOAD DBMS=WK1|WK3|WK4
DATA= <libref.SAS data-set> PATH=<'path-and-filename'>.WK1|.WK3| .WK4|<'filename'>| <fileref> ACCDESC= <libref.access-descriptor>' DELETE <'variable-identifier-1>...<'variable-identifier-n'> ERRLIMIT=<error-limit> FORMAT <SAS variable-name-1 >=<SAS format-1> ...<SAS variable-name-n>=<SAS format-n> LABEL LIMIT= <load-limit> LIST ALL | COLUMNS | FIELDS |<'variable-identifier'> LOAD PUTNAMES=YES | NO | Y | N RENAME <'variable-identifier-1'>= <'column-name-1'>...<'variable-identifier-n'> = <'column-name-n'> RESET ALL |<'variable-identifier-1'> <'variable-identifier-n'>... <'column-identifier-n' > C | N WHERE <SAS where-expression> RUN; |
The QUIT statement is also available in the DBLOAD procedure. It causes the procedure to terminate. QUIT is used most often in interactive line mode and batch mode to exit the procedure without exiting SAS.
WKn specific statements are as follows:
- PUTNAMES =YES|NO|Y|N
-
writes column names to the first row of the new WKn file. The column names are the SAS variable names. You can use the LABEL statement to assign SAS variable labels. You can modify the column names using the RENAME statement.
The PUTNAMES statement is optional. Omit PUTNAMES= and data is read from the data set and written to the WKn file. Data is written beginning in the first row of the WKn file. No column names are written to the file.
- FORMAT SAS variable-name-1 SAS format-1 <SAS variable-name-n SAS format-n>
-
assigns a temporary format to a SAS variable in the input SAS data set. This format temporarily overrides any other format for the variable. The assignment lasts only for the duration of the procedure. Assign formats to as many variables as you want in one FORMAT statement.
Use the FORMAT statement to change the format, column width, or the number of decimal digits for columns being loaded into the PC file. If you change the SAS variable format 12.1 to DOLLAR15.2, the column format of the loaded data changes. The fixed numeric format with a column width of 12 and one decimal digit changes to a currency format with a column width of 15 and two decimal digits.
| DBLOAD Procedure Data Conversions for WKn Files |
SAS data is read into Lotus 1-2-3 data when a table is loaded. In this conversion, SAS character data type is converted into the Lotus 1-2-3 label type. SAS numeric type is converted into the Lotus 1-2-3 number type.
The SAS/ACCESS interface converts SAS formats to the same or associated Lotus 1-2-3 formats and column widths. You can temporarily assign other formats and column widths to SAS variables with the FORMAT statement.
Note: The FORMAT statement in the DBLOAD
procedure changes only the format of SAS variables while you are creating
and loading the WKn files. When the procedure
finishes, the formats of the SAS variables return to their original settings. ![[cautionend]](../../../../common/63294/HTML/default/images/cautend.gif)
| 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 |
|
| Setting Environment Variables for WKn Files |
You can change the default behavior of the SAS/ACCESS interface by setting environment variables in your SAS configuration file. You can set four SAS/ACCESS environment variables: SS_MISS NULLS, SS_MIXED, SS_NAMES, and SS_SCAN. Setting these variables in your SAS configuration file changes how the interface works by default.
The configuration file omits these three environment variables, which means their default values are NO.
- SS_MISS NULLS
-
The DBLOAD procedure loads Lotus @NA cell values for missing values. Use this option to specify a null cell value instead. If set, missing values in a SAS data set
are displayed as blanks in the Lotus 1-2-3 table.
- SS_MIXED YES|NO
-
YES allows both Lotus 1-2-3 numeric and character data in a column to be displayed as SAS character data. The Lotus 1-2-3 numeric data is converted to its character representation when its corresponding SAS variable type is defined as character.
NO does not convert Lotus 1-2-3 numeric data in a column into SAS character data. Lotus 1-2-3 numeric data is read in as SAS missing values when its corresponding SAS variable type is defined as character. NO is the default.
Setting the SS_MIXED environment variable changes the default value of the MIXED statement in the Access procedure.
- SS_NAMES YES | NO
-
YES in the Access procedure generates SAS variable names from column names in the first row of the worksheet or specified range of the worksheet. Data is then read starting from the second row.
YES in the DBLOAD procedure writes column names using SAS variable names or SAS variable labels to the first row of the new WKn file. Data is then read from the data set and written to the WKn file beginning with the second row.
NO in the Access procedure generates the SAS variable names VAR0, VAR1, VAR2, and so on. It reads data from the first row of the worksheet or a specified range.
NO in the DBLOAD procedure reads the data from the data set and writes it to the WKn file beginning with the first row.
Setting the SS_NAMES environment variable changes the default value of the GETNAMES= option in the Access procedure, and the PUTNAMES= option in the DBLOAD procedure.
Default: NO - SS_SCAN YES | NO|number-of-rows
-
YES scans the data type and format of rows in a worksheet or specified range. If SKIPROWS= is specified, the first row is after skipping the number of rows specified. SS_SCAN finds the most common Lotus 1-2-3 data type and format to generate the SAS data type and format. If a number of rows is specified, the Access procedure scans only the data type and format from the specified rows.
NO uses the type and format of the first row in a worksheet or specified range. If SKIPROWS= is specified, the first row is after skipping the number of rows specified. NO is the default.
Default: No Number-of-rows scans only the type and format of the specified number of rows. Setting the number of rows is more efficient because data is read from only the specified number of rows rather than the entire file.
Setting the SS_SCAN environment variable changes the default value of the SCANTYPE= statement option in the Access procedure.
|
|
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.