SAS Component Language Dictionary

SETKEY

Defines an index key for retrieving rows from a SAS table

Category:

SAS Table

Syntax
Details
Examples
	Example 1: Defining an Index Key That Was Created Previously
	Example 2: Using a Composite Index Key with GE
	Example 3: Using an SCL List Instead of CALL SET
See Also

Syntax

nval=SETKEY(table-id<,key-name<,condition<,scroll-option<,list-id>>>>);

nval

contains the return code for the operation:

0	An active key was successfully set or cleared.
0	An error or warning condition occurred.

Type: Numeric

table-id

is the identifier that was assigned when the table was opened. If table-id is invalid, the program halts.

Type: Numeric

key-name

is the index key to be used for retrieval.

Type: Character

condition

specifies comparison criteria for the key value:

'EQ'	equal to the key value (This is the default.)
'GE'	greater than or equal to the key value
'GT'	greater than the key value
'LE'	less than or equal to the key value
'LT'	less than the key value.

Type: Character

scroll-option

specifies whether rows can be randomly retrieved:

'SCROLL': Rows can be retrieved in random order. (This is the default.)
'NOSCROLL': Rows can only be retrieved sequentially. This option improves performance when the table is accessed via the REMOTE engine and the IS mode is specified for the second argument of the OPEN function. Those options reduce the number of data transfer operations that are required when the table is read.

Type: Character

list-id

is the identifier for the list that contains values for the index key variables. You must use SETNITEMC and SETNITEMN to assign the values to the corresponding key variables in the list. An invalid list-id produces an error condition.

Type: Numeric or List

Details

SETKEY enables you to set an active key in an open table to a simple or composite key. It establishes a set of criteria for reading SAS table rows by comparing the value of the columns from the SDV to the key value in the rows.

Using a composite key with SETKEY operates the same way as the WHERE function only when the condition is 'EQ'. The value returned when the condition is 'EQ' is the same as if the columns specified in the composite key are connected by WHERE conditions using AND or ALSO.

For all other conditions (GT, GE, LT, or LE) specified with SETKEY for a composite key, the composite key columns are concatenated to form the index key. The number returned by the KEYCOUNT function is the number of rows in the table that satisfy the composite key. For example, if the composite index consists of columns SEX and AGE and the condition is GT (greater than), the values to search for are concatenated such that key values of F for SEX and 13 for AGE yield an index key of F13. Because the search is performed on the concatenated values, some values that you did not expect may meet a search condition. For example, key values of M for SEX and 11 for AGE meet the search condition, because the string M11 is considered greater than the string F13. If the active key is a composite key and the condition parameter is set to GT, GE, LT, or LE, the table subsets into rows whose primary key column values meet the specified criteria. Consequently, you still have to check the values of other key parts against the data vector to narrow down the subset of rows.

SETKEY works only after SET is called in the SCL program or when a list identifier is passed. The list identifier must point to a list that contains the values of the index key columns. Once an active key is set through SETKEY, it remains active until

the table is closed
another key is set
the current setting is cleared by passing the table identifier alone to SETKEY.

The table is automatically positioned at the first row that meets the specified criteria. Use FETCH or FETCHOBS to read the row.

SETKEY returns an error code if a WHERE clause is in effect. Index keys cannot be used in conjunction with WHERE clauses.

Examples

Example 1: Defining an Index Key That Was Created Previously

Define an index key for the table MYDATA, which subsets the table into only those rows where the value of the AGE column is greater than or equal to 20:

   /* Assuming a simple key, AGE, has been defined */
age=20;
dsid=open('MYDATA','I');
call set(dsid);
rc=setkey(dsid,'age','ge');
do while(fetch(dsid) ne -1);
   name=getvarc(dsid,1);
   put name=;
end;

Example 2: Using a Composite Index Key with GE

Search the table CHILDREN for all boys who are 5 years old or older. The composite key ATTR, which is created by ICREATE, is used for retrieval. The values of the composite key columns are concatenated, and the search is performed on the combined value. In this example, the key selects rows where AGE||GENDER [ge] 5M. The FETCH function within the DO-loop returns all rows where AGE>=5. Because some of the rows may not have a matched concatenated key part, you need an additional check on the value of the GENDER column in order to skip unmatched rows.

dsid=open('children','v');
   /* Create a composite key ATTR with AGE */
   /* as primary key column                */
rc=icreate(dsid,'attr','age gender');
call set(dsid);
age=5;
gender='M';
rc=setkey(dsid,'attr','ge');
do while(rc=0);
      /* FETCH function applies the retrieval */
      /* criteria and retrieves all rows      */
      /* for which AGE >=5                    */
   rc=fetch(dsid);
   if (rc) then leave;
      /* Filter out rows with gender ne 'M' */
   if (upcase (gender) ne 'M') then continue;
   child=getvarc(dsid,varnum(dsid,'name'));
   put child=;
end;
rc = close (dsid);

Example 3: Using an SCL List Instead of CALL SET

Using an SCL list avoids possible name collisions. Also, it enables you to set the retrieval criteria for rows at run time instead of at compile time.

dsid = open ( 'children','v');
rc = icreate( dsid, 'attr','age gender');
list = makelist();
list = setnitemc (list,cval,'gender');
   /* cval contains the value of 'M' */
list = setnitemn (list,nval,'age');
   /* nval contains the value of 5 */
rc = setkey (dsid,'attr','ge','',list);
   /* Print out all names with */
   /* age >= 5 and gender= 'M'    */
do while ( rc= 0 );
rc = fetch (dsid);
if (rc) then leave;
   sex1 = getvarc (dsid, varnum(dsid, 'gender'));
   if (upcase (gender) ne 'M') then continue;
   child = getvarc (dsid, varnum(dsid, 'name'));
   put child=;
end;
rc = close (dsid);