topic Re: create integrity constraints in SAS Programming

create integrity constraints

Decay2020 — Thu, 21 Jul 2022 12:27:18 GMT

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.

Re: create integrity constraints

kleelasiva9 — Thu, 21 Jul 2022 13:24:15 GMT

Concatenate the datasets and remove duplicates.

data final;
set PRIMARY Secondary;
run;

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

Re: create integrity constraints

Decay2020 — Thu, 21 Jul 2022 13:38:24 GMT

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

Re: create integrity constraints

FreelanceReinh — Thu, 21 Jul 2022 13:54:04 GMT

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.