Capturing Visit Dates for Anchor Visits

[djbateman]

I have done this several times, but I cannot find past code or remember what I did even though I know this is a pretty simple task.  I think I'm just going brain dead and can't think anymore.

I have a table of patient visits and dates (DOV) and another table of visits and anchor visits (WINDOWS) so I can try to find out if a visit is outside of the protocol-specified window.  I cannot think how to identify the anchor date (the EVENTDT that corresponds with the ANCHOR visit).  In the end, I want to see the DOV table with a new variable called ANCHORDT that should have values of 2019-09-05 for Subject 001-001-001 and 2019-09-06 for Subject 001-001-004 (but the Termination visits should not have an anchor date since that is not found in the WINDOWS table).  This is a very simple example, but in real life there could be multiple anchor dates per subject (just depending on what visit is assigned to be the anchor for each following visit).

Here is my sample data:

proc sql;
	create table dov (SUBJID char(11), EVENT char(50), EVENTDT char(10));
	insert into dov (subjid, event, eventdt)
		values ('001-001-001', 'Screening', '2019-08-20')
		values ('001-001-001', 'Day -1', '2019-09-04')
		values ('001-001-001', 'Randomization', '2019-09-04')
		values ('001-001-001', 'Part A Treatment Period Day 1', '2019-09-05')
		values ('001-001-001', 'Part A Treatment Period Day 2', '2019-09-06')
		values ('001-001-001', 'Part A Treatment Period Day 3', '2019-09-07')
		values ('001-001-001', 'Part A Treatment Period Day 4', '2019-09-08')
		values ('001-001-001', 'Part A Treatment Period Day 5', '2019-09-09')
		values ('001-001-001', 'Termination', '2019-09-12')
		values ('001-001-004', 'Screening', '2019-08-22')
		values ('001-001-004', 'Day -1', '2019-09-05')
		values ('001-001-004', 'Randomization', '2019-09-06')
		values ('001-001-004', 'Part A Treatment Period Day 1', '2019-09-06')
		values ('001-001-004', 'Part A Treatment Period Day 2', '2019-09-07')
		values ('001-001-004', 'Part A Treatment Period Day 3', '2019-09-08')
		values ('001-001-004', 'Part A Treatment Period Day 4', '2019-09-09')
		values ('001-001-004', 'Part A Treatment Period Day 5', '2019-09-10')
		values ('001-001-004', 'Termination', '2019-09-13');
	create table windows (EVENT char(50), MIN_WINDOW num, MAX_WINDOW num, ANCHOR char(50));
	insert into windows (event, min_window, max_window, anchor)
		values('Screening', -28, -2, 'Part A Treatment Period Day 1')
		values('Day -1', -1, -1, 'Part A Treatment Period Day 1')
		values('Randomization', 1, 1, 'Part A Treatment Period Day 1')
		values('Part A Treatment Period Day 1', 1, 1, 'Part A Treatment Period Day 1')
		values('Part A Treatment Period Day 2', 2, 2, 'Part A Treatment Period Day 1')
		values('Part A Treatment Period Day 3', 3, 3, 'Part A Treatment Period Day 1')
		values('Part A Treatment Period Day 4', 4, 4, 'Part A Treatment Period Day 1')
		values('Part A Treatment Period Day 5', 5, 5, 'Part A Treatment Period Day 1')
		values('Part A Safety Follow-up', 7, 10, 'Part A Treatment Period Day 1');
quit;

[PhilC]

curious? Is your Anchor Day always going to be the first treatment day? i.e. "Part A Treatment Period Day 1"

A Strange numbering system for "days".  Am I understanding that there is not a "zero" day?  That makes the math funny, does it not?

[djbateman]

It's not always "Part A Treatment Period Day 1" in real life, but my simplified example does happen to be.

No, there is no Day 0, so I do need to adjust the windows for that visit.

[djbateman · Accepted solution]

I figured it out.  I thought it would be a simple single SQL query, but it was a little more work.

proc sort data=dov;
	by event;
proc sort data=windows;
	by event;
run;

data dov_ref;
	merge dov (in=a) windows (in=b);
	by event;
	if a&b;
run;
proc sort data=dov_ref;
	by subjid eventdt;
run;

proc sql;
	create table anchordt as
	select subjid, anchor, eventdt as ANCHORDT
	from dov_ref
	having event=anchor
	order by subjid;
quit;


data dov_window;
	merge dov_ref anchordt;
	by subjid;
run;