IMSTAT Procedure (Data and Server Management)

FETCH Statement

The FETCH statement is used to retrieve rows from an in-memory table. You can use the FETCH statement to retrieve calculated columns that are calculated according to a script as part of the request. The columns that are calculated this way do not persist beyond the time it takes to execute in the server.

Syntax

Optional Argument

FETCH Statement Options

Syntax

FETCH <variable-list> </ options>;

Optional Argument

variable-list

specifies the numeric and character variables to retrieve.

FETCH Statement Options

ARRAYSTART=n

specifies the starting element of an array when the record of an in-memory table represents a variable array. This is the case, for example, when a pricing cube from SAS High-Performance Risk is loaded into a server. There might then be 10,000 columns for a variable. Specifying the ARRAYSTART= and ARRAYLENGTH= options enables you to page through the data more conveniently.

ARRAYLENGTH=k

specifies the length of the array to fetched when the record of an in-memory table represents a variable array. Use this option with the ARRAYSTART= option.

DESCENDING=variable-name

DESCENDING=(variable-list)

specifies which variables of the ORDERBY= list are used with descending sort order. Specifying the DESCENDING= option by itself has no effect. The option is specified in addition to the ORDERBY= option. The following example specifies to fetch data on columns A and B of the active table ordered by ascending unformatted values of B and descending unformatted values of C.

Alias	DESC=
Example	fetch a b / orderby=(b c) descending=c;

FORMAT <=("format-specification", ...)>

specifies the formats to use for the variables. By default, the FETCH statement retrieves the unformatted values. If you specify the FORMAT option without a list of format names, then the server applies the default format that is associated with each variable.

Be aware that when you retrieve unformatted values and you create an output data set with the OUT= option, the variable information such as formats and labels are transferred to the output data set.

If you want to use the default format for a variable, specify an empty string. The following example uses the default format for column A and the $10 format for column B.

Alias	FORMATS=
Example	fetch a b / format=("", "$10");

FROM=first-index

specifies the index of the first row to retrieve (inclusive). The value for first-index is 1-based.

Default	FROM=1
Interaction	The value for FROM= is applied after the evaluation of a WHERE clause.

NOPREPARSE

prevents the procedure from pre-parsing and pre-generating code for temporary expressions, scoring programs, and other user-written SAS statements.

When this option is specified, the user-written statements are sent to the server "as-is" and then the server attempts to generate code from it. If the server detects problems with the code, the error messages might not to be as detailed as the messages that are generated by SAS client. If you are debugging your user-written program, then you might want to pre-parse and pre-generate code in the procedure. However, if your SAS statements compile and run as you want them to, then you can specify this option to avoid the work of parsing and generating code on the SAS client.

When you specify this option in the PROC IMSTAT statement, the option applies to all statements that can generate code. You can also exclude specific statements from pre-parsing by using the NOPREPARSE option in statements that allow temporary columns or the SCORE statement.

Alias

NOPREP

ORDERBY=variable-name

ORDERBY=(variable-list)

specifies one or more variables by which to order the results. The default sort order of the ORDERBY= variables is ascending in unformatted values and follows location and collation rules. If you want to arrange some ORDERBY= variables in descending sort order, then list the variable names in the DESCENDING= option (in addition to listing them in the ORDERBY= option).

If you want to assign a format to ORDERBY= variables, you can use the ORDERBYFORMAT= option. That option can also be used to specify which variables are sorted by formatted values and which variables are sorted by unformatted values.

ORDERBYFORMAT=("format-specification", ...)

specifies the formats to use for sorting of the ORDERBY= variables. By default, if you specify an ORDERBY= variable or variable list, the server sorts by the ascending unformatted values. If you want to apply unformatted value ordering for some ORDERBY= variables, and formatted value ordering for other ORDERBY= variables, you can specify the keyword _RAW_ for the variables to sort by unformatted value.

The following example specifies to retrieve unformatted values of columns Make, Model, Type, Invoice, and Mpg_City. The rows are retrieved in the order of ascending formatted value of Type, using the $ format, and descending unformatted values of Invoice.

Example

fetch make model type invoice mpg_city / 
    orderby=(type invoice)
    desc   =invoice
    orderbyformat=("$", "_RAW_");

OUT=libref.member-name

specifies the name of the data set to store the result set of the FETCH statement.

SAVE=table-name

saves the result table so that you can use it in other IMSTAT procedure statements like STORE, REPLAY, and FREE. The value for table-name must be unique within the scope of the procedure execution. The name of a table that has been freed with the FREE statement can be used again in subsequent SAVE= options.

SETSIZE

requests that the server estimate the size of the result set. The procedure does not create a result table if the SETSIZE option is specified. Instead, the procedure reports the number of rows that are returned by the request and the expected memory consumption for the result set (in KB). If you specify the SETSIZE option, the SAS log includes the number of observations and the estimated result set size. See the following log sample:

NOTE: The LASR Analytic Server action request for the STATEMENT
      statement would return 17 rows and approximately
      3.641 kBytes of data.

The typical use of the SETSIZE option is to get an estimate of the size of the result set in situations where you are unsure whether the SAS session can handle a large result set. Be aware that in order to determine the size of the result set, the server has to perform the work as if you were receiving the actual result set. Requesting the estimated size of the result set does consume resources on the server. The estimated number of KB is very close to the actual memory consumption of the result set. It might not be immediately obvious how this size relates to the displayed table, since many tables contain hidden columns. In addition, some elements of the result set might not be converted to tabular output by the procedure.

TEMPEXPRESS="SAS-expressions"

TEMPEXPRESS=file-reference

specifies either a quoted string that contains the SAS expression that defines the temporary variables or a file reference to an external file with the SAS statements.

Alias

TE=

TEMPNAMES=variable-name

TEMPNAMES=(variable-list)

specifies the list of temporary variables for the request. Each temporary variable must be defined through SAS statements that you supply with the TEMPEXPRESS= option.

Alias

TN=

TO=last-index

specifies the index of the last row to retrieve (inclusive). The value for last-index is 1-based.

Default	The default value is FROM=first-index + 19.
Interaction	The value for TO= is applied after the evaluation of a WHERE clause.