create integrity constraints

[Decay2020]

DATA PRIMARY;

INFILE cards delimiter=",";

input key$ version$;

cards;

a,v1

b,v1

c,v1

d,v1

;

run;

DATA Secondary;

INFILE cards delimiter=",";

input key$ version$;

cards;

a,v1

b,v1

c,v2

d,v2

;

run;

Hi guys,

I have 2 datasets as above. Now, i need to set integrity constraint on the data 'primary' such that 'version' does not get repeated after appending the data 'secondary'. and if the same version is there then no new rows should get append. Also, i want to display a message for rejected records. So after appending 'secondary' data, the final data should look like below:

Data Want;

KEY	Version
A	V1
B	V1
C	V1
D	V1
C	V2
D	V2

 Thanks. 

[kleelasiva9]

Concatenate the datasets and remove duplicates.

data final;
set PRIMARY Secondary;
run;

proc sort data=final noduprec dupout=dups;
by _all_;
run;

[Decay2020]

No, I need to do it using integrity contraints, like primary key or something. not just appending.

[FreelanceReinh]

Hi @Decay2020,

I don't think this is going to work with an integrity constraint on version alone because the duplicate values of this variable in dataset PRIMARY itself would contradict the requirement of no repeats. However, by adding variable key to the constraint you get what you want for your example data: a message in the log about the rejected observations and the desired dataset -- as a modified version of dataset PRIMARY, though, since integrity constraints do not apply to newly created datasets ("WANT").

proc datasets lib=work nolist;
modify primary;
ic create unique(key version);
quit;

proc append base=primary new=secondary;
run;

Log messages from PROC APPEND:

NOTE: Appending WORK.SECONDARY to WORK.PRIMARY.
WARNING: Add/Update failed for data set WORK.PRIMARY because data value(s) do not comply with integrity constraint _UN0001_, 2 observations rejected.
NOTE: There were 4 observations read from the data set WORK.SECONDARY.
NOTE: 2 observations added.
NOTE: The data set WORK.PRIMARY has 6 observations and 2 variables.