I have two datasets that I want to merg- One from the screening registry and the other one from a hospital registry. I want to include the hospital data into the screening dataset. However, I want the hospital data to be included in the row where date of screening is earlier than the date of diagnosis. For example, if someone got screened in October 2007 and November 2018 (screening data) and diagnosed with the disease in December 2007 (hospital data), I want the hospital data for that person to be included in the row of October 2007 screening data. An example is below.

Any help on how to write the syntax for this. Thank you.


Data: Screening
ID Gender date_screened
1 1 01Nov2000
1 1 05Oct2003
1 1 01Oct2006
1 1 02Mar2008
2 2 01Feb2000
2 2 17Apr2003
2 2 09June2005
2 2 07Oct2008
3 1 08Dec2008
4 1 01Oct2006
4 1 02Mar2008
5 1 17Apr2003


Data: hospital
ID Disease_type Date_diagnosis
1 1 02Nov2001
2 1 13June2004
2 2 08July2005
3 1 16Nov2013
5 1 18Apr2005


Combine to (an example) :

ID Gender Date_screened Disease_type Date_diagnosis
1 1 01Nov2000 1 02Nov2001
1 1 05Oct2003
1 1 01Oct2006
1 1 02March2008
2 2 01Feb2000
2 2 17Apr2003 1 13June2004
2 2 09June2005 2 08July2005
2 2 07Oct2008
3 1 08Dec2008 1 16Nov2013
4 1 01Oct2006
4 1 02Mar2008
5 1 17Apr2003 1 18Apr2005