How select a minimum difference between two variables for a specific combination of variable values?

[ulricius]

Hi all,

I'm struggling with how to map transit trips onto a timetable, by finding, for each observed date, absolute minimum values of differences between observed trip boarding times and timetable (actual) departure times. Let me explain by showing a simple example. First, the trip data (from card trajectories):

CardID;LineRoute;Date;BoardTime;FromStop;ToStop
1;1;24NOV2016;07:30:10;1;2
1;1;24NOV2016;16:30:15;2;1
1;1;25NOV2016;07:30:10;1;2
1;2;25NOV2016;08:10:15;2;3
1;2;25NOV2016;16:15:00;3;2
1;1;25NOV2016;16;45;30;2;1
2;1;24NOV2016;07:45:10;7;8
2;1;24NOV2016;16:35:15;8;7
2;1;25NOV2016;07:15:10;7;8
2;2;25NOV2016;08:30:15;8;9
2;2;25NOV2016;16:18:00;9;8
2;1;25NOV2016;16;48;30;8;7

Then, the possible from-stop-->to-stop for each lineroute (previously merged onto the trips) is found in the timetable for Route1 and Route2:

LineRoute;ServiceTripID;Date;FromStop;ToStop;DepTime;
1;1;24NOV2016;1;2;07:25:00;
1;2;24NOV2016;1;2;07:30:00;
1;3;24NOV2016;1;2;07:35:00;
1;4;24NOV2016;1;2;08:05:00;
1;5;24NOV2016;1;2;08:10:00;
1;6;24NOV2016;1;2;08:15:00;
1;7;24NOV2016;1;2;16:15:00;
1;8;24NOV2016;1;2;16:20:00;
1;9;24NOV2016;2;1;16:25:00;
1;10;24NOV2016;2;1;16:30:00;
1;11;24NOV2016;2;1;16:35:00;
1;12;24NOV2016;7;8;07:25:00;
1;13;24NOV2016;7;8;07:30:00;
1;14;24NOV2016;7;8;07:35:00;
1;15;24NOV2016;7;8;08:05:00;
1;16;24NOV2016;7;8;08:10:00;
1;17;24NOV2016;7;8;08:15:00;
1;18;24NOV2016;7;8;16:15:00;
1;19;24NOV2016;7;8;16:20:00;
1;20;24NOV2016;8;7;16:25:00;
1;21;24NOV2016;8;7;16:30:00;
1;22;24NOV2016;8;7;16:35:00;
2;1;24NOV2016;2;3;08:05:00;
2;2;24NOV2016;2;3;08:10:00;
2;3;24NOV2016;2;3;08:15:00;
2;4;24NOV2016;3;2;16:15:00;
2;5;24NOV2016;3;2;16:20:00;
2;6;24NOV2016;3;2;16:25:00;
2;7;24NOV2016;8;9;08:05:00;
2;8;24NOV2016;8;9;08:10:00;
2;9;24NOV2016;8;9;08:15:00;
2;10;24NOV2016;9;8;16:15:00;
2;11;24NOV2016;9;8;16:20:00;
2;12;24NOV2016;9;8;16:25:00;
1;1;25NOV2016;1;2;07:25:00;
1;2;25NOV2016;1;2;07:30:00;
1;3;25NOV2016;1;2;07:35:00;
1;4;25NOV2016;1;2;08:05:00;
1;5;25NOV2016;1;2;08:10:00;
1;6;25NOV2016;1;2;08:15:00;
1;7;25NOV2016;1;2;16:15:00;
1;8;25NOV2016;1;2;16:20:00;
1;9;25NOV2016;2;1;16:25:00;
1;10;25NOV2016;2;1;16:30:00;
1;11;25NOV2016;2;1;16:35:00;
1;12;25NOV2016;7;8;07:25:00;
1;13;25NOV2016;7;8;07:30:00;
1;14;25NOV2016;7;8;07:35:00;
1;15;25NOV2016;7;8;08:05:00;
1;16;25NOV2016;7;8;08:10:00;
1;17;25NOV2016;7;8;08:15:00;
1;18;25NOV2016;7;8;16:15:00;
1;19;25NOV2016;7;8;16:20:00;
1;20;25NOV2016;8;7;16:25:00;
1;21;25NOV2016;8;7;16:30:00;
1;22;25NOV2016;8;7;16:35:00;
2;1;25NOV2016;2;3;08:05:00;
2;2;25NOV2016;2;3;08:10:00;
2;3;25NOV2016;2;3;08:15:00;
2;4;25NOV2016;3;2;16:15:00;
2;5;25NOV2016;3;2;16:20:00;
2;6;25NOV2016;3;2;16:25:00;
2;7;25NOV2016;8;9;08:05:00;
2;8;25NOV2016;8;9;08:10:00;
2;9;25NOV2016;8;9;08:15:00;
2;10;25NOV2016;9;8;16:15:00;
2;11;25NOV2016;9;8;16:20:00;
2;12;25NOV2016;9;8;16:25:00;

What I would like to have is something like the following - a table where the trip observations (first "have" table) has been merged with the most probable service trip from the second "have" table, based on line route, date, fromstop, tostop and the minimum absolute difference between the recorded boarding time and the departure time of the trip in the timetable. Thus, something like...:

CardID;LineRoute;Date;BoadingTime;FromStop;ToStop;ServiceTripID;FromStop;DepTime
1;1;24NOV2016;07:30:10;1;2;2;1;07:30:00
1;1;24NOV2016;16:30:15;2;1;10;2;16:30;00
1;1;25NOV2016;07:30:10;1;2;2;1;07:30:00
1;2;25NOV2016;08:10:15;2;3;2;2;08:10:00
1;2;25NOV2016;16:15:00;3;2;5;3;16:15:00
1;1;25NOV2016;16;45;30;2;1;11;2;16:35:00
2;1;24NOV2016;07:45:10;7;8;14;7;07:35:00
2;1;24NOV2016;16:35:15;8;7;22;8;16:35:00
2;1;25NOV2016;07:15:10;7;8;14;7;07:35:00
2;2;25NOV2016;08:30:15;8;9;9;8;08:15:00
2;2;25NOV2016;16:18:00;9;8;11;9;16:20:00
2;1;25NOV2016;16;48;30;8;7;22;8;16:35:00

How should I proceed?

[andreas_lds · Accepted solution]

Maybe:

1. Combine both datasets and calculate the absolute difference between BoardTime and DepTime.
2. Select the observation with minimum absolute difference for each trip.

[ulricius]

Thanks - that's actually what I intend to do now (the join process is running at the moment, but the file will be huge). After the join, I will use proc means to find the minimum value of the differences between boarding time and departure time for each combination of date, from stop, to stop and line route (I haven't generated a trip index, however I'm considering it now). Is that what you mean, approximately?

[ballardw]

Since you say you are already running a Join you might share the code.

[ulricius]

sure! First step (join):

proc sql;
create table data.temp as
select
a.*,
b.*
 from data.have1_trips a full join data.have2_timetable b
 on a.stp		=b.FromStp
where 
a.date=b.date and
a.lineroute=b.lineroute and 
a.boardingstop=b.FromStp and
a.alightingstop=b.ToStp;
 quit;
 run;

data step to determine differences between boarding time and departure times:

proc sort data=data.alightingstop; by crd_num trs_dt time FromStp ToStp Total_Mete; run;

data data.temp;
set data.temp;
BoardingStop=FromStp;
AlightingStop=ToStp;
timediff=abs(boardingtime-deptime);
run;

Final step, the determination of most probable alighting stop using the means procedure:

proc means data=data.temp noprint;
var timediff;
by CardID date lineroute BoardingStop AlightingStop;
output out=data.want min=seconds;
run;

But I suspect that there are more efficient ways to obtain this!