Examples of Creating SPD Server Indexes

Example 1: Creating SPD Server Indexes in a DATA Step

Example 2: Creating SPD Server Indexes with PROC DATASETS

Example 3: Creating SPD Server Indexes By Using SQL

Example 4: Creating SPD Server Indexes Using Pass-Through SQL

Example 5: Using VERBOSE= to See Index Information

Example 6: Using PROC SORT with SPD Server

Example 7: Using the Implicit SPD Server BY Clause Sort

Example 8: Using PROC SORT

Example 1: Creating SPD Server Indexes in a DATA Step

The following code creates SPD Server table MYTABLE. The code uses the INDEX= table option to create a simple SPD Server index X on column X, and a composite SPD Server index Y on columns (A B).

    data mylib.mytable(index=(x y=(a b)));
      x=1;
      a="Doe";
      b=20;
    run;

Example 2: Creating SPD Server Indexes with PROC DATASETS

The following code creates simple and composite SPD Server Indexes on table MYTABLE.

    PROC DATASETS lib=mylib;
      modify mytable;
      index create x;
      index create y=(a b);
    quit;

Example 3: Creating SPD Server Indexes By Using SQL

The following code creates the same simple and composite SPD Server indexes that were created in Example 2, but uses PROC SQL instead of PROC DATASETS.

PROC SQL;
    create index x on mylib.mytable(x);
    create index y on mytable(a,b);    
quit;

Example 4: Creating SPD Server Indexes Using Pass-Through SQL

The following code creates the same simple and composite SPD Server indexes that were created in Example 2, but uses pass-through SQL instead of PROC DATASETS.

PROC SQL;
    connect to sassped (dbq="path1" server=host.port user='anonymous');
    execute( create index x on mytable(x) ) by sasspds;
    execute( create index y on mytable(a,b) ) by sasspds;    
quit;

Example 5: Using VERBOSE= to See Index Information

Sometimes you want to see information about indexes that are associated with a particular table. The PROC CONTENTS table option VERBOSE= provides additional detail about all of the indexes that are associated with an SPD Server table. For example, the following PROC CONTENTS code uses the VERBOSE= option to show details about two indexes::

PROC CONTENTS data=mainhs.class (verbose=yes);
run;

The result shows the minimum and maximum values for the two indexes in the table and the number of discrete values for each index:

Alphabetic List of Index Info:				.
Index         												Name
KeyValue (Min):                 			Alfred
KeyValue (Max):                 			William
# of Discrete values:            			19
Index         												age_sex
KeyValue (Min):                 			11.000000
KeyValue (Max):                 			16.000000
# of Discrete values:            			11
Data Partsize													16776672

Example 6: Using PROC SORT with SPD Server

If you use PROC SORT with SPD Server, your table is sorted. But suppose that you submit a PROC SORT statement to sort a table that was not previously indexed, or that was sorted on the table's BY column.

In this scenario, PROC SORT takes advantage the fact that SPD Server sorts implicitly and asserts BY clause ordering to the SPD Server. This process performs the sort on the SPD Server machine, but there is still significant I/O between the client node and the SPD Server machine. The sorted data makes a round trip from the server machine to the client machine, and then back again. Fortunately, the SQL pass-through facility in SPD Server offers an extension to SQL to allow a table copy and sort operation, all on the server machine.

To avoid inefficiency, eliminate PROC SORT statements from your SAS jobs where possible. Instead, make SAS procedures and DATA steps that require BY clause processing use SPD Server's implicit sorts.

Example 7: Using the Implicit SPD Server BY Clause Sort

   /* The following DATA step performs a server sort on the   */
   /* table column PRICE. There is no prior index for PRICE.  */

      data _null_;
      set sport.expraqs;
        by price;
           if (string='nat') then do;
           put '*' @@;
           price = price - 30.00;
        end;
      put raqname @30 price;

Example 8: Using PROC SORT

      /* The following PROC SORT performs a server sort on the  */
      /* table column MODEL. There is no prior index for MODEL. */

        PROC SORT
          data=inventory.old_autos
          out=inventory.old_autos_by_model;
          by model;
        run;