BookmarkSubscribeRSS Feed
Obsidian | Level 7

I have an APPEND process that duplicates data if I run it twice. Is there some way to avoid data duplication using APPEND?


Rogerio Alves
Super User
Add some code after PROC APPEND to remove the duplicated obs . Like:

proc append base=have data=temp force;run;
proc sql;
create table want as
select distinct * from have;
Calcite | Level 5
Tks a lot for suggestion! Do you think proc sort could be more performatic?
Super User
I think both have same performance , due to both take multi session to run.
But you could test it . I welcome you post the compare result .
Calcite | Level 5
Tks a lot for all attention here. I will try both and I let you know.
Tourmaline | Level 20

For production ready code, you shouldn't be able to run the process twice with the same data.

If you can't gurantee that, redesign the job to either have an Update loading strategy, or as @Ksharp suggests - have a post process that removes duplicates.

This might be fine if your data is manageble, but it will affect the performance for sure.

Data never sleeps
Obsidian | Level 7

To avoid a problem to duplicate data with an APPEND I created an INDEX on DI with some columns. When I run the job it is executed but send this message and JOB is finished with EXIT on FLOW MANAGER.



Add/Update failed for data set <TABLE TARGET> because data value(s) do not comply with integrity constraint.


How could I solve that?



Rogerio Alves
Rhodochrosite | Level 12

Hi @Rogerio_Alves 


I wonder how you come to get duplicate data. The DI Studio Append Transformation (at least in my version 4.9) always deletes the output table before appending (check the transformation code in properties -> code), so it should be impossible to get duplicates unless your output table is input to a step before the append, so existing data goes into the appendtogether with new data.


It is a little bit difficult to imagine what's going on, so please post a screenshot of your job canvas.



Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 5 in conversation