There are three separated tables. They look like this:
table 1: table 2: table 3:
subject shot_time subject measurement_a measurement_a_time subject measurement_b measurement_b_time
1 6:35 pm 1 10 6:23 pm 1 20 5:10 pm
1 7:35 pm 1 11 6:24 pm 1 22 5:12 pm
2 5:25 am 1 12 6:23 pm 1 26 5:50 pm
2 7:15 am 1 15 6:23 pm 1 21 6:10 pm
3 3:35 pm 2 9 5:23 pm 1 13 6:30 pm
3 6:15 pm 2 13 6:23 pm 1 22 7:20 pm
3 7:35 pm 2 10 7:23 pm 1 29 8:10 pm
I am trying to find the most recent measurement_a and measurement_b from table 2 and table 3 for each shot in table 1. For instance, the first shot for the subject 1 happened in 6:35 pm, then I am trying to get the fourth row in table 2 and fifth row in table 3. And for the second shot of subject 1, I am looking for the fourth row in table 2 and sixth row in table 3. Then I will be able to use some filter to see if each shot is necessary(say measurement_a must be >=12 and measurement_b must be <=30).
I have trouble to do that either use proc sql or data step. I am a new SAS user and hope this question won't get too much laugh.... Any input will be appreciated!
Thank you!
Try something like this:
proc sql;
create table t4 as
select t1.subject, shot_time, a_time, measurement_a, b_time, measurement_b
from
t1 left join
t2 on t1.subject=t2.subject left join
t3 on t1.subject=t3.subject
where a_time<shot_time and b_time<shot_time
group by t1.subject, t1.shot_time
having a_time = max(a_time) and b_time=max(b_time);
quit;
PG
Try something like this:
proc sql;
create table t4 as
select t1.subject, shot_time, a_time, measurement_a, b_time, measurement_b
from
t1 left join
t2 on t1.subject=t2.subject left join
t3 on t1.subject=t3.subject
where a_time<shot_time and b_time<shot_time
group by t1.subject, t1.shot_time
having a_time = max(a_time) and b_time=max(b_time);
quit;
PG
Thank you so much PG! The max function is awesome!
Would you mind if I ask a following question? What if I want to capture the last two readings for these two measurements?
Well, there isn't any builtin function to get the second latest time of a series, but that might not be what you really need. The measurements that remain relevant at shot time might be those taken within a certain time before the shot? Those could be identified rather simply with a simple change in the query :
%let relevantDelay='00:20:00't; /* Look for measurements taken within 20 minutes before shot time */
proc sql;
create table t4 as
select t1.subject, shot_time, a_time, measurement_a, b_time, measurement_b
from
t1 left join
t2 on t1.subject=t2.subject left join
t3 on t1.subject=t3.subject
where a_time<shot_time and b_time<shot_time
group by t1.subject, t1.shot_time
having a_time >= max(a_time)-&relevantDelay. and b_time.>=max(b_time)-&relevantDelay.;
quit;
PG
Thank you so much, PG! This is so helpful!
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.