[FIXED] How can i avoid duplication in a proc append even if i run it twice?

Issue

I can't just delete duplication from my base table because i have a DATETIME column so it will not be considered like duplication.

Exemple : base (after first append) :

+------+----------------------------+------+-----+
| name | date                       |  id  |year |  
+------+----------------------------+------+-----+
|    X |Thursday06july2020 16:21:06 |   303| 2019| 
|    Y |Thursday06july2020 16:21:06 |   91 | 2020|  
+------+----------------------------+------+-----+

after doing some modification on my data table i will have this in :

+------+----------------------------+------+-----+
| name | date                       |  id  |year |  
+------+----------------------------+------+-----+
|    W |Friday07August2020 13:27:15 |  92  | 2019|
|    X |Friday07August2020 13:27:15 |  303 | 2019| 
|    Y |Friday07August2020 13:27:15 |  91  | 2020|  
|    Z |Friday07August2020 13:27:15 |  45  | 2020|
+------+----------------------------+------+-----+

then i want to reexcute the proc append. Is there any method to compare all the columns expect the date column ? I wish that it's clear enough.

Solution

Create a primary key on all of your columns except date in your base table. For example:

data basetable;
    length pk $50.;
    set basetable;
 
    pk = cats(name, id, year);
run;

You can use this to update values and columns within your data.

To continue using proc append, you can create an integrity constraint on pk that prevents duplicates on your base table.

proc datasets lib=mylib nolist;
    modify basetable;
        ic create unique (pk);
quit;

This integrity constraint will be destroyed if you recreate the table.

Answered By - Stu Sztukowski

Answer Checked By - Candace Johnson (PHPFixing Volunteer)