PROC SQL INDEXES

[dataMart87]

Do the two statements below create different kinds of indexes?  The variable MRN has unique values.

    CREATE INDEX MRN ON TASK1(MRN);

    CREATE UNIQUE INDEX MRN ON TASK1(MRN);

Running on SAS 9.4M1

[ChrisBrooks]

They do - the first allows duplicate index values and the second doesn't e.g.

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         data class;
 74         set sashelp.class;
 75         run;
 
 NOTE: There were 19 observations read from the data set SASHELP.CLASS.
 NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 76         
 77         
 78         proc sql;
 79         create index sex on class(sex);
 NOTE: Simple index sex has been defined.
 80         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 81         
 82         
 83         data class;
 84         set sashelp.class;
 85         run;
 
 NOTE: There were 19 observations read from the data set SASHELP.CLASS.
 NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 86         
 87         proc sql;
 88         create unique index sex on class(sex);
 ERROR: Duplicate values not allowed on index Sex for file CLASS.
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 89         quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 90         
 91         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 104        

[dataMart87]

Thanks, Chris.  The variable/column MRN has unique values.

[PGStats]

With the UNIQUE property, future insertions into your table will forbid duplicate keys.

[dataMart87 · Accepted solution]

Ahh.  Thanks, PG.