Looks like there are several scenarios that need to be handled
1. If the date in dataset B is earlier by 6 months or less
2. If the date in dataset B is later by 6 months or less
3. If the date in dataset B is exactly the same date
We need to define their order of precedence as there are entries that can fit two of the above scenarios, so we can decide what will be the final "measurement_b" value to take.
This is what I have so far, you can play around with the order of the if-then handling below to suit what outcome you want to achieve
data ds1;
set ds1;
time + 1; /*add counter*/
by id date;
if first.id then time = 1;
run;
data ds;
set ds1 ds2; /*combine so they can be sorted by date*/
run;
proc sort data=ds;
by id date; /*sorting*/
run;
data look_both_ways (keep=id date measurement_a time keep rename=(keep=measurement_b) where=(time ne .));
set ds;
by ID;
set ds (firstobs=2 keep=date measurement_b rename=(date=next_date measurement_b=nxt_m_b))
ds (obs=1 keep=time);
format prev_date date9.;
prev_date=ifn(first.ID,(.),lag(date));
next_date=ifn(last.ID,(.),next_date);
prev_m_b = ifn( first.Id, (.), lag(measurement_b));
nxt_m_b = ifn(last.Id, (.), nxt_m_b);
if intck('month',prev_date,date) <= 6 then keep=prev_m_b; /*scenario 1*/
if intck('month',date,next_date) <= 6 then keep=nxt_m_b; /*scenario 2*/
if date=next_date then keep=nxt_m_b; /*scenario 3*/
run;
... View more