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!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.