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.
Concatenate the datasets and remove duplicates.
data final;
set PRIMARY Secondary;
run;
proc sort data=final noduprec dupout=dups;
by _all_;
run;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.