Creating SPD Server Indexes Examples

Creating SPD Server Indexes from a DATA Step

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

The code above creates SPD Server table X. Next, the code creates a simple SPD Server index X on column X, and a composite SPD Server index Y on columns (A B).

Creating SPD Server Indexes from PROC DATASETS

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

This creates the same simple and composite SPD Server indexes that were created in Example 1, assuming that the same DATA step was executed without index creation included.

Creating SPD Server Indexes Using SQL

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

This creates the same simple and composite SPD Server indexes as in Example 1, assuming that the same DATA step was executed without index creation included.

Creating SPD Server Indexes Using Pass-Through SQL

    PROC SQL;
    connect to sasspds (
       dbq="path1"
       server=host.port
       user='anonymous');

      execute(create index x on x (x))
      by sasspds;

      execute(create index y on x (a,b))
      by sasspds;
    quit;

This creates the same simple and composite SPD Server indexes as in Example 1, assuming that the same DATA step was executed without index creation included.

Using VERBOSE= to See Index Information

There will be times when you want to see information about indexes that are associated with a particular table. The table option VERBOSE= provides details of all indexes associated with an SPD Server table. For example, if the code from Example 2 above is followed with the expression below:

    PROC CONTENTS
      data=sports.expraqs
      (verbose=yes);
    run;

The following will be output:

       Alphabetic List of Index Info:
Bitmap Index (No Global Index):         GRIPSIZE
KeyValue (Min):                 4.250000
KeyValue (Max):                 5.000000
# of Discrete values:           3

Using PROC SORT with SPD Server

If you use PROC SORT with SPD Server, your table is sorted. However, you should understand a few things first. For example, assume 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.

PROC SORT takes advantage of SPD Server sorting implicitly and asserts BY clause ordering to the SPD Server. This 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.

Using 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;

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;