SAS Component Language Dictionary

KEYCOUNT

Returns the number of rows that meet the criteria specified by an index key

Category:

SAS Table

Syntax
Details
Examples
	Example 1: Using a Simple Index Key
	Example 2: Using a Composite Index Key with Condition 'EQ'
	Example 3: Using a Composite Index Key with Condition 'GT'
See Also

Syntax

nrow=KEYCOUNT(table-id);

nrow

contains the number of rows that meet the criteria, or <0 if an error occurred. The error message can be retrieved by using SYSMSG.

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

Details

KEYCOUNT returns the number of rows that meet the criteria specified by the index key column. The index key column was specified with the last SETKEY function that was used on the table. After KEYCOUNT executes, the table points to the first row that meets the criteria defined by the last SETKEY function. Use FETCH or FETCHOBS to read the row.

CAUTION:

Using KEYCOUNT with composite keys may show a larger number of rows matching the search criteria than you expect.

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. (See Example 1.)

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 that the KEYCOUNT function returns is the number of rows in the table that satisfy the composite key. For example, if the composite index consists of columns GENDER and AGE and the condition is GT (greater than), the values to search for are concatenated such that key values of F for GENDER 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 the search condition . For example, key values of M for GENDER and 11 for AGE meet the search condition, because the string M11 is considered greater than the string F13. (See Example 2.) [cautionend]

Examples

Suppose you have the following examples which use rows from the WORK.CLASS table. Create a simple index for the table WORK.CLASS, using ICREATE or the DATASETS procedure, with AGE as the index column. Also, create a composite index for WORK.CLASS called COMP that consists of columns GENDER and AGE.

Example 1: Using a Simple Index Key

Set up a search criteria of AGE=13. SETKEY specifies that the key column is AGE and the condition is equality.

/*  Locate rows where 'age = 13'  */
   tableid = open( 'work.class', 'v' );

      /* Create the simple index */
   rc = icreate(tableid,'age');
   name = ''; gender = '';
   age = 13;
   call set(tableid);
   rc = setkey(tableid,'age','eq');
   nrow = keycount(tableid);

   if (nrow < 0) then _msg_ = sysmsg();
   else
      do;
         put 'Number of rows found:' nrow;
         do while (fetch(tableid) ne -1);
            put name= gender= age=;
         end;
      end;

This program produces the following output:

Number of rows found: 3
   name=Alice gender=F age=13
   name=Becka gender=F age=13
   name=Jeffery gender=M age=13

Example 2: Using a Composite Index Key with Condition 'EQ'

Set up search criteria of GENDER=F and AGE=13. SETKEY specifies that the key is named COMP and the condition for the search is equality.

/* Locate rows where 'gender="F"' */
/* and 'age=13' */
   tableid = open( 'work.class', 'v' );

      /*  Create index */
   rc = icreate(tableid,'comp','gender age');
   name = ''; gender = 'F'; age = 13;
   call set(tableid);
   rc = setkey(tableid,'comp','eq');
   nrow = keycount(tableid);

   if (nrow < 0) then _msg_ = sysmsg();
   else
   do;
      put 'Number of rows found:' nrow;
      do while (fetch(tableid) ne -1);
         put name= gender= age=;
      end;
   end;

This program produces the following output:

Number of rows found: 2
   name=Alice gender=F age=13
   name=Becka gender=F age=13

Example 3: Using a Composite Index Key with Condition 'GT'

Set up search criteria of GENDER=F and AGE greater than 13. SETKEY specifies that the key is named COMP and the condition for the search is greater-than. This example illustrates the unexpected results returned by KEYCOUNT when you use composite index keys and SETKEY using a 'GT' argument.

/*  Locate rows where 'genderage' > 'F13'  */
   tableid = open( 'work.class', 'v' );

      /* Create index */
   rc=icreate(tableid,'comp','gender age');
   name = ''; gender = 'F'; age = 13;
   call set(tableid);
   rc = setkey(tableid,'comp','gt');
   nrow = keycount( tableid);

   if (nrow < 0) then _msg_ = sysmsg();
   else
   do;
      put 'Number of rows found:' nrow;
      do while (fetch(tableid) ne -1);
         put name= gender= age=;
      end;
   end;

This program lists 14 rows from the indexed table that met the search criteria of GENDER||AGE>=F13.

Number of rows found: 14
   name=Gail gender=F age=14
   name=Tammy gender=F age=14
   name=Mary gender=F age=15
   name=Sharon gender=F age=15
   name=Thomas gender=M age=11
   name=James gender=M age=12
   name=John gender=M age=12
   name=Robert gender=M age=12
   name=Jeffrey gender=M age=13
   name=Alfred gender=M age=14
   name=Duke gender=M age=14
   name=Guido gender=M age=15
   name=William gender=M age=15
   name=Philip gender=M age=16

You can see that James at AGE=12 does not meet the SETKEY requirement of AGE > 13 and GENDER > 'F'. However, his row was selected because the values were concatenated before the comparison was made.