I have 2 tables(sample dataset and a restriction dataset attached in the post) that I need to merge on site id. Is there a way that I can loop through the restrictions and merge with sample dataset at the same time? Whatever samples that did not merge with a restriction the first time will merge with the next restriction. Until all the restrictions are apply to samples. For samples with site id that is not in the restriction, the restriction will be none. Will appreciate any inputs. Thanks!
Sample dataset:
sampleid sampletype subjectid siteid
a1111 WB 11111 1007
a2222 Serum 22222 1010
a3333 Plasma 33333 1003
a4444 WB 44444 1023
a5555 Serum 55555 1007
a6666 Plasma 66666 1010
a7777 WB 11111 1007
a8888 Serum 33333 1003
a9999 Plasma 99999 1005
result:
sampleid sampletype subjected siteid exp_date restriction1
a1111 WB 11111 1007 7/26/2038 Destroy Samples
a2222 Serum 22222 1010 7/26/2038 Destroy Samples
a3333 Plasma 33333 1003 7/26/2048 Destroy Samples
a4444 WB 44444 1023 7/26/2038 Destroy Samples and Data
a5555 Serum 55555 1007 7/26/2038 Destroy Samples
a6666 Plasma 66666 1010 7/26/2038 Destroy Samples
a7777 WB 11111 1007 7/26/2038 Destroy Samples
a8888 Serum 33333 1003 7/26/2048 Destroy Samples
a9999 Plasma 99999 1005 7/26/2038 None
restriction dataset: