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;
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;
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;
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.