IMSTAT Procedure (Data and Server Management)

STORE Statement

The STORE statement enables you to assign the contents of previously saved tables to macro variables. You can reuse the results from one statement as input for subsequent statements in the same IMSTAT procedure.

Syntax
Required Arguments
Optional Arguments
Details
Using the STORE Statement
Listing Column Names from ODS Tables
Examples
Example 1: Accessing Multi-Part Table Output and Storing a Single Value
Example 2: Storing Variable Names
Example 3: Storing Values from Multiple Cells
Example 4: Storing Values from Vertically Stacked Cells

Syntax

STORE table-name <[table-number]>
(row-number | _ALL_ | _LAST_ | row-list | WHERE="where-clause" <,>
column-number | _ALL_ | COLS=column-list) = macro-variable-name </ options>;

Required Arguments

column-number

specifies the column number to access as it appears in the default output or with the REPLAY statement. Be aware that hidden columns that might appear in an output data set when an ODS table is converted to a SAS data set are not counted. The first column is numbered 1. You can specify _ALL_ as an alternative, or specify the column names in the COLS= option.

macro-variable-name

specifies the name of a macro variable to use for storing the value.

table-name

specifies the saved result table to use.

row-number

specifies the row number to access as it appears in the default output or with the REPLAY statement. The first row is numbered 1. You can specify _ALL_, _LAST_, a numeric list of rows (row-list), or a WHERE clause as alternatives. When you specify a row-list, any row number less than 1, greater than the number of rows, and duplicate row numbers, are ignored.

If you specify a WHERE= clause, you can use Boolean expressions like NOT, BETWEEN, CONTAINS, LIKE, and mathematical expressions. The following operators are available:
  • prefix +, prefix -
  • <>, ><, ** (max, min, and power)
  • *, /
  • infix +, infix -
  • || (concatenation)
  • =, ^=, <, <=, >, >=, IN(set), IS NULL, IS MISSING
  • AND, &
  • OR, |
Examples The following row-list accesses rows 0, 1, and 2 from the results.
(0, 1, 2)
The following row-list accesses rows 0, 1, 2, 4, 8, 12, 16, and 20.
(0 to 2 by 1, 4, 8 to 20 by 4)

Optional Arguments

[table-number]

specifies the table to use for accessing multi-part tables. In the following example, the HISTOGRAM statement generates one histogram table for the Cylinders variable and a second for the EngineSize variable. The two histogram tables are stored in the temporary HistTab table. In order to access the second histogram table, the [2] is used. If you do not specify a table-number, the first table is used.

Example 
proc imstat data=mylasr.cars(tag=sashelp);;
    histogram Cylinders EngineSize / save=HistTab;
    store HistTab[2](2,6) = Engsz_Pct;
quit;
%put &Engsz_Pct;

CONTROL="control-string"

specifies the string that used to format cell values before they are stored in the macro variable. The control string must include the same number of placeholders as the number of columns. The default placeholder is %. There are two more placeholders besides %, # and ^. If the i-th placeholder in CONTROL= is:

  • #, then it is a placeholder for the value of the i-th relevant column in the first relevant row
  • ^, then it is a placeholder for the value of the i-th relevant column in the last relevant row
In the preceding statements, first means the relevant row with the smallest row number and last means the relevant row that with the largest row number. See Example 4.
If you read character values that you want to use in a WHERE clause, you might need to enclose the placeholders in quotation marks.

LEFT="left-side-string"

specifies the string to assign as a prefix to the macro variable.

NODUPS

specifies to ignore duplicate formatted cell values.

Restriction This option applies to numeric values only and when only one column is accessed from the results.

RIGHT="right-side-string"

specifies the string to assign as a suffix to macro variable.

SEPARATOR="separator-string"

specifies the string to use for separating the formatted cell values.

Default " " (the space character)

Details

Using the STORE Statement
Listing Column Names from ODS Tables

Using the STORE Statement

The simplest use of the STORE statement is to read a value from a cell in the results, assign it to a macro variable, and use it in subsequent statements. The following statement is copied from part of Example 3 and demonstrates reading the value of a single cell into a macro variable.
store mpgtab (_last_, cols=Mean) = avgmpgcity;
More sophisticated uses of the STORE statement are possible. Example 2 shows how variable names are read from a results table and used in a subsequent programming statement.
Perhaps the most sophisticated use of the STORE statement is to construct a string that can be used in a WHERE clause. Such a use typically requires use of the LEFT=, CONTROL=, SEPARATOR=, and RIGHT= options. The following steps describe the concept and flow:
  1. Specify the cells from the results to use by specifying the rows and columns.
  2. Use options to control how to construct the macro value, made up of cell values and the following:
    1. A string to prefix to the left side of the macro variable. Typically, "(" is common.
    2. A control string to use for formatting cell values for each row.
    3. A string to use for separating the formatted control strings. The control string and the separator are used.
    4. A string to use as a suffix for the right side of the macro variable. Typically, ")" is common.
For examples that demonstrate using these options, see Example 3 and Example 4.

Listing Column Names from ODS Tables

In order to use the STORE statement, you need to save the ODS table output with a statement that supports the SAVE= option, such as summary mpg_city / save=mpgtab;. The ODS output is shown in Example 3.
You can reference the mean value with a statement like store mpgtab(1,6) = meanMpgCity; because the Mean column is the sixth column. However, it is more robust to reference columns by name, such as store mpgtab(where="Column eq 'MPG_City'", cols=Mean) = meanMpgCity;.
To determine the column names, like Column and Mean, you need to know the ODS table name for the statement in the IMSTAT procedure and to use the TEMPLATE procedure. The following example shows how to identify the column names that are available in a saved table from the SUMMARY statement. 
proc template;
    source LASR.IMSTAT.Summary;  1
run;
1 The LASR.IMSTAT portion of the source statement is common to all statements in the IMSTAT procedure. The name of the table, such as Summary, is provided in the documentation for each statement. 
define table Lasr.Imstat.Summary;
   notes "Descriptive Statistics";
   dynamic TableName;
   column Table GroupBy Column Min Max N Sum Mean Std StdErr CV CSS 
          USS TValue ProbT NMiss Bin;
   header h1;

   define h1;
      text "Summary Statistics for Table " TableName;
      space = 1;
      spill_margin;
...
The SAS log shows the column names. Table isn’t used unless the ODS output is written to a SAS data set. The GroupBy column is not used because the GROUPBY= option was not used in the SUMMARY statement. The remaining columns are available and you must specify the column name as it is shown. The names are case-sensitive.

Examples

Example 1: Accessing Multi-Part Table Output and Storing a Single Value

The following STORE statement reads the value from the second row of the Percent column into the Bin2Pct macro variable. 
proc imstat data=example.cars(tag=sashelp);
    histogram Cylinders EngineSize / save=HistTab;
    store HistTab[2](2,cols= percent) = Bin2Pct;
quit;
%put &Bin2Pct;
The following display shows the HISTOGRAM output for the EngineSize variable.
Histogram for Column EngineSize
The PUT statement shows the value from the Bin2Pct macro variable in the SAS log. The macro variable can show a different number of significant digits than the table output. 
NOTE: The string 19.626168224 related to table histtab has been stored in the 
         macro variable bin2pct.

%put &bin2pct;
19.626168224

Example 2: Storing Variable Names

The following DISTINCT statement calculates the number of unique values for the numeric variables and stores the results in DistinctTab. 
proc imstat data=example.cars(tag=sashelp);
    distinct _numeric_ / save=distincttab; 
run;
The following display shows the results.
Number of Distinct Values
The following STORE statement reads the results and filters for rows with a number of distinct values that is greater than 100. The values from the first column (the variable names) are stored in the macro variable VarList1. 
    store distincttab (where="NDistinct > 100", 1) = varlist1;
run;
The SAS log shows the constructed string: 
NOTE: The string MSRP Invoice Horsepower Weight related to table distincttab 
         has been stored in the macro variable varlist1.
The following CORR statement uses the variable names that were stored in the VarList1 macro variable. 
    corr &varlist1;
run;
The following display shows the results of the CORR statement.
Pairwise Correlation

Example 3: Storing Values from Multiple Cells

proc imstat data=example.cars(tag=sashelp);
    summary mpg_city / save=mpgtab;
run;
The following display shows the results. The overall average Mpg_City for all makes and models is highlighted.
Summary Statistics for Mpg_City
The following STORE statement stores the overall average Mpg_City value in the AvgMpgCity macro variable. Then, a summary of the same Mpg_City variable is requested, but grouped by Make. The results are saved in SummaryTab. 
 store mpgtab (_last_, cols=Mean) = avgmpgcity;
run;
    summary mpg_city / groupby=make save=summarytab;
run;
The following display shows the part of the results.
Summary Statistics for Mpg_City grouped by Make
The following STORE statement accesses the results (from SummaryTab) and filters for rows with an above average Mpg_City value. The LEFT=, CONTROL=, SEPARATOR=, and RIGHT= options are used to build a string, substituting each value of Make for % in the CONTROL= option. 
    store summarytab (where="Mean > &avgmpgcity",cols=Make) = highmpgmakes /
          left="Make in (" control="'%'" separator=", " right=")"; run;
The SAS log shows the constructed string. Notice how the LEFT= value is used to begin the string, and the CONTROL= and SEPARATOR= values are used for each row in the results. The RIGHT= value is used to end the string. 
NOTE: The string Make in ('Honda', 'Hyundai', 'Kia', 'MINI', 'Mazda', 
        'Mitsubishi', 'Oldsmobile', 'Pontiac', 'Saab', 'Saturn', 'Scion',
        'Subaru', 'Suzuki', 'Toyota', 'Volkswagen') related to table 
        summarytab has been stored in the macro variable highmpgmakes.
Finally, the constructed string is used in a WHERE clause. The CROSSTAB statement shows the frequency of each MPG_City value for each Make that is specified in the WHERE clause. 
where &highmpgmakes;
    crosstab mpg_city*make;
run;
The following display shows part of the crosstabulation results.
Crosstabulation of Mpg_City by Make

Example 4: Storing Values from Vertically Stacked Cells

The following PRECENTILE statement calculates the quantiles for the Mpg_City variable and saves the results in MpgTab. 
proc imstat data=example.cars(tag=sashelp);
    percentile mpg_city / save=mpgtab;
run;
The following display shows the results.
Percentiles and Quantiles for Mpg_City
To create a WHERE clause that uses the values for the third quartile of the Mpg_City variable (between 19 and 21.5), the following STORE statement is used. 
    store mpgtab (where="Pctl >= 50", cols=Value Value) = q3 / 
          control="(mpg_city between # and ^)";
run;
The STORE statement performs the following:
  • Filters the results to the rows where Percentile is greater than or equal to 50 (two rows).
  • Reads the Value column (twice).
  • Substitutes the first value in the first row for the # placeholder. Substitutes the second value from last row in the ^ placeholder, because it is the second placeholder.
  • Stores the resulting string (mpg_city between 19 and 21.5) in the Q3 macro variable.
The SAS log shows the constructed string: 
NOTE: The string (mpg_city between 19 and 21.5) related to table mpgtab has been 
         stored in the macro variable q3.
The following statements then use a constructed string in a WHERE clause and request a summary of the Mpg_City variable that is grouped by Make. 
where &q3.;
    summary mpg_city / groupby=make;
run;
The following display shows part of the results.
Summary Statistics for Mpg_City grouped by Make
Copyright © SAS Institute Inc. All rights reserved.