Index Creation Techniques

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

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

The following PROC DATASETS code creates a simple index and a composite index on server table MyTable.

    proc datasets lib=spdslib;
      modify mytable;
      index create x;
      index create y=(a b);
    quit;

The following code creates the same simple and composite server indexes that were created in the previous example using PROC SQL.

proc sql;
    create index x on spdslib.mytable(x);
    create index y on spdslib.mytable(a,b);    
quit;

The following code uses SQL explicit pass-through to create a simple index and a composite index:

proc sql;
    connect to sasspds (dbq="Conversion_Area" server=husky.spdsname
user='siteusr1' prompt=yes);
    execute( create index x on mytable(x) ) by sasspds;
    execute( create index y on mytable(a,b) ) by sasspds;    
quit;

This example creates a SAS table named patient_info and uses PROC DATASETS to create indexes for the table. The SPDSIASY macro variable is set to request parallel execution. The MODIFY statements in the PROC DATASETS request are specified in a way that will support parallel execution.

  data foo.patient_info;
     length
       last_name $10
       first_name $20
       patient_class $2
       patient_sex $1;

     patient_no=10;
       last_name="Doe";
       first_name="John";
       patient_class="XY";
       patient_age=33;
       patient_sex="M";

  run;

  %let spdsiasy=YES;
  proc datasets lib=foo;
     modify patient_info;
        index create
          patient_no
          patient_class;
     run;
     modify patient_info;
        index create
          last_name
          first_name;
     run;
     modify patient_info;
        index create
          whole_name=(last_name first_name)
          class_sex=(patient_class patient_sex);
     run;
  quit;

Indexes for PATIENT_NO and PATIENT_CLASS are created in parallel, indexes for LAST_NAME and FIRST_NAME are created in parallel, and indexes for WHOLE_NAME and CLASS_SEX are created in parallel.