topic Re: How select a minimum difference between two variables for a specific combination of variable val in SAS Programming

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

ulricius — Mon, 23 Mar 2020 13:21:41 GMT

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?

Re: How select a minimum difference between two variables for a specific combination of variable val

andreas_lds — Mon, 23 Mar 2020 14:34:23 GMT

Maybe:

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

Re: How select a minimum difference between two variables for a specific combination of variable val

ulricius — Mon, 23 Mar 2020 14:57:25 GMT

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?

Re: How select a minimum difference between two variables for a specific combination of variable val

ballardw — Mon, 23 Mar 2020 17:18:30 GMT

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

Re: How select a minimum difference between two variables for a specific combination of variable val

ulricius — Mon, 23 Mar 2020 18:13:06 GMT

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!