How to select the most recent entry from a different data set

[Rocktd]

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!

[PGStats · Accepted solution]

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

[Rocktd]

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?

[PGStats]

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

[Rocktd]

Thank you so much, PG! This is so helpful!