topic DI APPEND in SAS Data Management

DI APPEND

Rogerio_Alves — Tue, 09 Feb 2021 17:14:09 GMT

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

Re: DI APPEND

Ksharp — Wed, 10 Feb 2021 12:29:11 GMT

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;
quit;

Re: DI APPEND

LinusH — Wed, 10 Feb 2021 13:31:20 GMT

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.

Re: DI APPEND

ralves2 — Wed, 10 Feb 2021 14:44:23 GMT

Tks a lot for suggestion! Do you think proc sort could be more performatic?

Re: DI APPEND

Ksharp — Thu, 11 Feb 2021 11:16:05 GMT

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 .

Re: DI APPEND

ralves2 — Thu, 11 Feb 2021 12:09:33 GMT

Tks a lot for all attention here. I will try both and I let you know.

DI APPEND -

Rogerio_Alves — Thu, 25 Feb 2021 11:18:08 GMT

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.

ERROR ON LOG:

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

How could I solve that?

Thanks!

Re: DI APPEND -

ErikLund_Jensen — Wed, 03 Mar 2021 18:39:11 GMT

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.