We often need to think about duplicate keys in our data.  This example will show you the most common way to identify duplicate keys and then another much more efficient way using hash tables. 

 

Consider the National Drug Code (NDC).  This is a composite key consisting of the Labeler Code, the Product Code, and the Package Code.  Let's go through our dataset and flag all rows that have a duplicate key so we can then decide which we want to keep and what we want to do with the others.  Or maybe we just want to find the median of some of the values in the duplicated rows and set an imputation flag.  We have lots of options once we know which rows are duplicates.

Typically, we code something like the following to flag duplicate keys.

Proc sort data=DataWithPossibleDups out=_data_ ;

    By LblCode, ProdCode, PkgCode ;
Run ;
Data DataWithFlaggedDups ;
    Set _last_ ;
    By LblCode, ProdCode, PkgCode ;
    If not ( first.PkgCode and last.PkgCode ) then flg_dupNDC = 1 ;
Run ;

While this code is short and easy to understand, there are two problems:

Here's another way.  We can use hash tables.  These are direct-lookup tables in memory that have methods to work with the tables.  We will use the add() and check() methods.  Yes, the code is longer and more complicated, but it runs in O(2n) time and we have not changed the order of our data.

Data DataWithFlaggedDups ;
 /* In order to set the flg_dupNDC flag, we need two hash
    tables: one to check for duplicates and one to store the
    duplicate keys we find. */
    If 0 then set DataWithPossibleDups ;
    Declare hash distinctKeys() ;
    distinctKeys.defineKey( "LblCode" , "ProdCode" , "PkgCode" ) ;
    distinctKeys.defineDone() ;
    Declare hash dupKeys() ;
    dupKeys.defineKey( "LblCode" , "ProdCode" , "PkgCode" ) ;
    dupKeys.defineDone() ;
 /* Now we need to load these two hash tables. */
    Do i=1to nObs ;
     /* Walk through our data file using the point= dataset option. */
        Set DataWithPossibleDups nObs=nObs point=i ;
     /* Load the key in the distinctKeys hash table and, if the key was
        already there, load it in the dupKeys hash table. */
        If ( distinctKeys.add() ne 0 ) then rc = dupKeys.add() ;
        Drop rc ;
    End ;
    Do i=1to nObs ;
     /* Walk through our data file again, using the point= dataset
        option. */
        Set DataWithPossibleDups nObs=nObs point=i ;
     /* Set flg_dupNDC to 1 if the key is in the dupKeys hash object.
        Since we have never returned to the top of the DATA step, we
        will need to explicitely set flg_dupNDC to missing if the key is
        not in the dupKeys hash table. */
        If dupKeys.check()
            then flg_dupNDC = . ;
            Else flg_dupNDC = 1 ;
        Output ;
    End ;
 /* Don't forget to force a stop or you will be in an infinite loop! */
    Stop ;
Run ;

Now that we have flagged the duplicate keys, we can apply other business rules to determine which rows we want to keep.