BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rocktd
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

4 REPLIES 4
PGStats
Opal | Level 21

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

PG
Rocktd
Calcite | Level 5

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
Opal | Level 21

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

PG
Rocktd
Calcite | Level 5

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2259 views
  • 4 likes
  • 2 in conversation