topic Re: How to select the most recent entry from a different data set in SAS Programming

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

Rocktd — Wed, 04 Jun 2014 23:41:56 GMT

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!

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

PGStats — Thu, 05 Jun 2014 02:15:05 GMT

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;

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

Rocktd — Thu, 05 Jun 2014 16:13:58 GMT

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?

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

PGStats — Thu, 05 Jun 2014 17:02:08 GMT

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;

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

Rocktd — Mon, 09 Jun 2014 18:25:27 GMT

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