Matching Dates with Visit Names

[djbateman]

I have a dataset that has a list of visits and dates for each subject along with an anchor visit and a visit window.  I want to create a variable that displays the visit date that matches the anchor visit.  Below is a subset of my data and my method for getting the anchor date.  I can get everything to line up with the Day 1 anchor because Day 1 is found within itself, but I can't get the Day 10 or Day 11 anchors to be detected because there is no match within those anchor subsets.  If you run the code below and open the DOV_REFDATE table, you will see what I mean.  I just need the REF_DATE column to be fully populated.  Any suggestions?

proc sql;
	create table dov (SUBJID char(11), EVENT char(50), EVENTDT char(10), MIN_WINDOW num, MAX_WINDOW num, ANCHOR char(50));
	insert into dov (subjid, event, eventdt, min_window, max_window, anchor)
		values('001-001-073','Screening',				'2019-07-09',-28,-2,'Part B Day 1')
		values('001-001-073','Day -1',					'2019-08-05', -1,-1,'Part B Day 1')
		values('001-001-073','Part B Day 1',			'2019-08-06',  1, 1,'Part B Day 1')
		values('001-001-073','Randomization',			'2019-08-06',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 2',			'2019-08-07',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 3',			'2019-08-08',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 4',			'2019-08-09',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 5',			'2019-08-10',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 6',			'2019-08-11',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 7',			'2019-08-12',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 8',			'2019-08-13',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 9',			'2019-08-14',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 10',			'2019-08-15',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 11',			'2019-08-16',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 12',			'2019-08-17',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 13',			'2019-08-18',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Day 14',			'2019-08-19',  1, 1,'Part B Day 1')
		values('001-001-073','Part B Safety Follow-up',	'2019-08-23',  7,10,'Part B Day 10')
		values('001-001-081','Screening',				'2019-08-29',-28,-2,'Part B Day 1')
		values('001-001-081','Day -2',					'2019-09-18', -2,-2,'Part B Day 1')
		values('001-001-081','Day -1',					'2019-09-19', -1,-1,'Part B Day 1')
		values('001-001-081','Randomization',			'2019-09-19',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 1',			'2019-09-20',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 2',			'2019-09-21',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 3',			'2019-09-22',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 4',			'2019-09-23',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 5',			'2019-09-24',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 6',			'2019-09-25',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 7',			'2019-09-26',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 8',			'2019-09-27',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 9',			'2019-09-28',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 10',			'2019-09-29',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 11',			'2019-09-30',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 12',			'2019-10-01',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 13',			'2019-10-02',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 14',			'2019-10-03',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Day 15',			'2019-10-04',  1, 1,'Part B Day 1')
		values('001-001-081','Part B Safety Follow-up',	'2019-10-07',  7,10,'Part B Day 11');
quit;

proc sql;
	create table dov_refdate (keep=subjid anchor event eventdt ref_date max_window min_window) as
	select a.*, b.eventdt as ref_date
	from dov as a left join (select subjid, event, eventdt from dov where event=anchor) as b
	on a.subjid=b.subjid & a.anchor=b.event;
quit;

[Patrick · Accepted solution]

Does below return what you're after?

proc sql;
  create table dov_refdate as
    select 
      a.*, 
      b.eventdt as ref_date
    from 
      dov a 
      left join 
      dov b
        on 
          a.subjid=b.subjid 
          and a.anchor=b.event
    order by a.subjid, b.eventdt
  ;
quit;

[djbateman]

That seems to have done the trick!  I knew someone out there would know the solution.  It looks like my problem is the subsetting of the second DOV reference where I am specifying that EVENT must equal ANCHOR.  Thank you so much for your help!