I think this can get you started.
I suspect it may return multiple records though, so you may need to add another filter or add another layer of logic if that is happening.
Try running it on your actual data and see if you run into issues with duplicates. If that's the case post back with some sample data that shows a few duplicates. You may also not be 100% accurate here because of the missing data.
It depends a lot on your data but if some one didn't do a survey but did do a battery and there was a survey in that time period it would link. This also isn't testing that there's only a unique match to each one. I would see how it works though and then post back with issues, just giving you some ideas of what you'll need to check out. You may get lucky with mostly clean data 🙂
data WORK.DSST_MEANS_SAMPLE;
infile datalines dsd truncover dlm=',';
input Subject_ID_byDate:$6. Time_Task_Start:TIME8. Date_Task_Start:MMDDYY10. DSST_RT_Median:32. DSST_Correct_Responses:BEST12.;
format Time_Task_Start TIME8. Date_Task_Start MMDDYY10. DSST_Correct_Responses BEST12.;
datalines;
1601SR, 15:18:28, 04/04/2016, 0.781493399, 95
1601SR, 17:13:32, 04/04/2016, 0.748181999, 99
1601SR, 19:06:48, 04/04/2016, 0.765006255, 97
1601SR, 21:08:41, 04/04/2016, 0.731959533, 100
1601SR, 8:42:43, 04/05/2016, 0.781481933, 94
;;;;
data WORK.REDCAP_SAMPLE;
infile datalines dsd truncover dlm=',';
input performance_peers:BEST12. effort_peers:BEST12. date_task_start:MMDDYY10. Time_Task_Start:TIME8.;
format performance_peers BEST12. effort_peers BEST12. date_task_start MMDDYY10. Time_Task_Start TIME8.;
datalines;
4, 3, 04/04/2016, 17:28:00
4, 3, 04/04/2016, 19:21:00
4, 2, 04/04/2016, 21:24:00
5, 2, 04/05/2016, 8:57:00
4, 3, 04/05/2016, 10:54:00
;;;;
data redcap1;
set redcap_sample;
*adds ids;
id = _n_;
run;
data dsst1;
set dsst_means_sample;
*adds ids;
id = _n_;
run;
proc sql;
create table linked as
select a.id as task_id,
a.date_task_start as battery_date_start,
a.time_task_start as battery_time_start,
b.id as survey_id,
b.date_task_start as survey_date_start,
b.time_task_start as survey_time_start,
a.time_task_start as interval_start,
a.time_task_start + 30*60 as interval_end format=time8.,
case when missing(task_id) then 'No Battery'
when missing(survey_id) then 'No Survey'
else 'Match'
end as status
from dsst1 as a
full outer join redcap1 as b
on a.date_task_start = b.date_task_start
and b.time_task_start between a.time_task_start and (a.time_task_start + 30*60)
order by a.id, b.time_task_start;
quit;
proc sort data=linked;
by task_id survey_date_start;
run;
Click image to register for webinar
