PROC SQL INDEXES

dataMart87 — Mon, 27 Aug 2018 22:54:42 GMT

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

Re: PROC SQL INDEXES

ChrisBrooks — Mon, 27 Aug 2018 23:34:18 GMT

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

Re: PROC SQL INDEXES

dataMart87 — Tue, 28 Aug 2018 00:26:20 GMT

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

Re: PROC SQL INDEXES

PGStats — Tue, 28 Aug 2018 02:08:08 GMT

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

Re: PROC SQL INDEXES

dataMart87 — Fri, 31 Aug 2018 17:49:51 GMT

Ahh. Thanks, PG.

topic Re: PROC SQL INDEXES in SAS Procedures

PROC SQL INDEXES

Re: PROC SQL INDEXES

Re: PROC SQL INDEXES

Re: PROC SQL INDEXES

Re: PROC SQL INDEXES