Hello, I was wondering if there is a way to count observations based on parameters given in the current observation. To me this sounds like a macro, however I'm not too familiar with those and maybe you guys know how to do it in a data step or proc sql. My data looks like this: FL_DATE CARRIER FL_NUM ORIGIN DEST crs_dep_time crs_arr_time min_connex_time max_connex_time 2012-01-01 AA 2400 LAX DFW 0:20 5:05 6:05 10:05 2012-01-01 AA 2043 SFO DFW 0:25 5:35 6:35 10:35 2012-01-01 AA 2538 LAS ORD 0:45 6:15 7:15 11:15 2012-01-01 AA 2297 JFK MIA 5:35 8:45 9:45 13:45 2012-01-01 AA 1845 BOS MIA 5:40 9:15 10:15 14:15 2012-01-01 AA 1776 DFW PHL 6:10 10:05 11:05 15:05 2012-01-01 AA 2042 DFW FLL 6:35 10:05 11:05 15:05 2012-01-01 AA 392 ORD LGA 7:15 10:20 11:20 15:20 2012-01-01 AA 1137 ORD MSY 7:20 9:35 10:35 14:35 2012-01-01 AA 1762 MIA ATL 14:00 16:00 17:00 21:00 The goal is to count for each flight number (fl_num) how many other flight numbers are able to connect. I think I should not get too much into detail but mathematically I would explain it as this: n=1 = current obs; N+k = each following min_connex_time(n) < crs_dep_time (n+k) < max_connex_time (n) AND Dest(n) = Origin (n+k) In words, the first observation describes a flight from Los Angeles to Denver. This can be connected to any flight in the following observations that fullfills the conditions, i.e. departing from the origin where the current flight arrived at and departing within the given timespan of min and max_arr_time This should generate a count variable, which displays how many times the condition was met. The result should look like this: FL_DATE CARRIER FL_NUM ORIGIN DEST crs_dep_time crs_arr_time min_connex_time max_connex_time Connex 2012-01-01 AA 2400 LAX DFW 0:20 5:05 6:05 10:05 2 2012-01-01 AA 2043 SFO DFW 0:25 5:35 6:35 10:35 1 2012-01-01 AA 2538 LAS ORD 0:45 6:15 7:15 11:15 1 2012-01-01 AA 2297 JFK MIA 5:35 8:45 9:45 13:45 0 2012-01-01 AA 1845 BOS MIA 5:40 9:15 10:15 14:15 1 2012-01-01 AA 1776 DFW PHL 6:10 10:05 11:05 15:05 0 2012-01-01 AA 2042 DFW FLL 6:35 10:05 11:05 15:05 0 2012-01-01 AA 392 ORD LGA 7:15 10:20 11:20 15:20 0 2012-01-01 AA 1137 ORD MSY 7:20 9:35 10:35 14:35 0 2012-01-01 AA 1762 MIA ATL 14:00 16:00 17:00 21:00 0 Let me know if I need to clarify anything. Thanks in advance for any hints. Kind regards, Sandun Edit: I edited one flight number. They are usually distinct values, but in some instances of through flight two observations have the same number. But I will deal of that problem later
... View more