Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

Dealing with Duplicate Keys

Suppose we have a data set sorted by a variable called "age", and want to check whether any of the age values are repeated. We can use PROC SORT to sort the data by key and simultaneously remove any duplicate records that are present, the following example demonstrates this.

proc sort data=sashelp.class out=agelist nodupkey ;
  by age;
run;

The following notes are reported in the log showing how many records have been removed. PROC SORT will retain the first occurrence of each value of age.

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: 13 observations with duplicate key values were deleted.

The only problem with this is that we have no record of which rows were removed. If this information is required we can utilise the DUPOUT option. The DUPOUT options allow us to remove duplicates as demonstrated in the first example but also retain those rows in a separate data step. The following example demonstrates the use of DUPOUT:

proc sort data=sashelp.class 
   out=agelist nodupkey 
   dupout=dup_age_recs;
  by age;
run;

Viewing the log messages reveals that two new data sets have been created, on with the unique values of age and a second containing all of the removed values.

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: 13 observations with duplicate key values were deleted.
NOTE: The data set WORK.AGELIST has 6 observations and 5 variables.
NOTE: The data set WORK.DUP_AGE_RECS has 13 observations and 5 variables.

There are many ways to deal with duplicates within the SAS language including the DATA STEP and PROC SQL.

For further information on these and PROC SORT view the SAS Help pages or visit the SAS online support ate www.support.sas.com.