The GROUPBY statement derives the grouping hierarchy of the distinct formatted values for the specified variables. If no list of variable names is specified, the grouping hierarchy is computed for all variables in the active table. The statement can return a section of all distinct groupings to the client or save the entire grouping set as a temporary table in the server.
lists the aggregator on which the ordering of the result set is based.
MAX | maximum value |
MEAN | arithmetic mean |
MIN | minimum value |
N | counts the nonmissing values of the weight variable |
SUM | sum of the weight values |
Alias | AGG= |
Default | SUM |
specifies to arrange the returned grouping hierarchy of the variables in descending order of the item rankings. If this option is not specified, the returned items are arranged in ascending order. When combined with the LIMIT=n option, the GROUPBY statement can either return the top n or the bottom n distinct groupings.
Interaction | The DESCENDING option is ignored if the TEMPTABLE option is specified. |
specifies the numeric frequency variable that is used to compute the ranking of a distinct grouping. When this option is specified, the AGGREGATE= and WEIGHT= options are ignored. The following GROUPBY statement requests the top 5 groupings of Region and then Product from the Prdsale table. The groupings are rank ordered by the sum of the Actual column:
Example | proc imstat data = mylasr.prdsale;
groupby region product / freq = actual limit = 5;
run; |
specifies the maximum number of distinct groupings to be returned. When combined with the DESCENDING option, the GROUPBY statement can either return the top n or the bottom n distinct groupings. The value for n must be a positive integer. For example, the commands below return the bottom 5 groupings according to their Score values:
Default | 0 |
Interaction | This option is ignored if the TEMPTABLE option is specified. |
Tip | If n is zero, then all distinct groupings are returned (up to 231–1). With high-cardinality data sets, setting n to zero can significantly delay the response of the server. |
Example | proc imstat data = mylasr.prdsale;
groupby region product / weight = actual
agg = max
valuegt = ("West", "Chair")
limit=5;
run; |
specifies that missing values are excluded in the determination of GROUPBY values. By default, levels with missing values are included.
Alias | NOMISS |
specifies that the temporary table that is generated by the TEMPTABLE option is not partitioned by the group-by variables. When you create a temporary table with the GROUPBY statement, by default, the server partitions the table and each partition has a single row. When the number of groups is large, this results in many tiny partitions and requires additional memory resources to store the partition information for the temporary table.
specifies the rank ordering to use for sorting the distinct groupings. The following rank-order types are valid in the GROUPBY statement:
FREQ | frequency count of the variables |
VALUE | formatted values of the variables |
WEIGHT | aggregate values of the WEIGHT= variables |
Default | FREQ |
specifies that when the active table is partitioned, then the variable-list that you specify for the GROUPBY statement is expanded to include the partition variables. The partition variables are added to the beginning of the variable-list.
Example | data mylasr.cars(partition=(type)); set sashelp.cars; run; proc imstat data=mylasr.cars; groupby origin make / partition weight=invoice agg=max descending limit=5 ; run; |
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.
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.
specifies the exclusive lower bound of the numeric rank order scores of the distinct groupings to return. All distinct groupings with numeric rank order scores that are greater than f are returned.
Alias | SGT= |
Interaction | This option is ignored if the TEMPTABLE option is specified. |
specifies the exclusive upper bound of the numeric rank order scores of the distinct groupings to return. All distinct groupings with numeric rank order scores that are less than f are returned.
Alias | SLT= |
Interaction | This option is ignored if the TEMPTABLE option is specified. |
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= |
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= |
generates an in-memory temporary table from the result set. The IMSTAT procedure displays the name of the table and stores it in the &_TEMPLAST_ macro variable, provided that the statement executed successfully.
specifies the exclusive lower bound of the variable’s formatted values of the distinct groupings to return. All distinct groupings with formatted values for the variable that are lexicographically greater than the specified bound are returned.
Alias | VGT= |
Interaction | This option is ignored if the TEMPTABLE option is specified. |
specifies the exclusive upper bound of the variable’s formatted values of the distinct groupings to return. All distinct groupings with formatted values for the variable that are lexicographically less than the specified bound are returned.
Alias | VGT= |
Interaction | This option is ignored if the TEMPTABLE option is specified. |
specifies the formats for the variables. If you do not specify the VARFORMATS= option, the default formats are applied for the variables.
specifies the numeric weight variable to use for computing the rank order score of a distinct grouping.
Interaction | When the WEIGHT= option is specified, the server sets the ORDER= option to ORDER=WEIGHT. |