BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dataMart87
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
dataMart87
Quartz | Level 8

Ahh.  Thanks, PG.

View solution in original post

4 REPLIES 4
ChrisBrooks
Ammonite | Level 13

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
Quartz | Level 8

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

PGStats
Opal | Level 21

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

PG
dataMart87
Quartz | Level 8

Ahh.  Thanks, PG.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 2245 views
  • 0 likes
  • 3 in conversation