Question about filtering data - please help!

[Anya]

Hi,

I'm a SAS newbie, and I have a pretty simple question:

I have about 20 data sets representing specific sections of a road network, and each have 2 columns that I am interested in: Vehicle_ID and Time_Step. At each time step (1 second), the data set lists the the vehicles which are at a particular position on that section of the road. I would like to match the Vehicle_ID in all tables and obtain the vehicles that are travelling along the entire route in the

network. For each of these vehicles, I would like to find the total travel from from the time steps. I was wondering if there is a simple way in which to filter the data in each data set to give me the

Vehicle_IDs and a count of the corresponding Time_Steps in which the vehicle appeared on the section. For example, if Vehicle 2242 was found to be in the data set at Time_Steps 43, 44, 45, 46, 47, 48 then it was travelling for 6 seconds on that section. Its a relatively simple calculation but I am having trouble performing the filtering on 20+ large data sets.

I would really appreciate it anyone could give me some advice on how I might be able to do this in SAS or forward my question to someone who could help me.

Thanks in advance!

Anya

[art297]

A couple of questions:

Does it have to be a filtering specific solution or can it be a datastep or proc sql solution

Is there a chance that vehciles might have missing Time_Steps and, if so, what would you want as a result if a vehicle had something like Time_Steps 43, 44, 46, 47, 48? or 1,2,3,46,47,48?

[Anya]

Hello!

1. It doesn't have to be a filtering specific solution.

2. I think the time steps would generally be in a sequence because a simulation tracked the vehicle's position through a particular section for every second - so its not likely that the time steps will look like 1,2,3,46,47,48 because (hopefully) no vehicles get sucked into a black hole of traffic while travelling on a section

[art297 · Accepted solution]

Then wouldn't something like the following suffice?:

data have;

  input vehicle time_step;

  cards;

1 23

1 24

1 25

2 52

2 53

2 54

2 55

3 34

3 35

;

proc sql;

  create table want as

    select distinct vehicle,

      max(time_step)-min(time_step) +1

       as seconds

         from have

           group by vehicle

  ;

quit;

[Anya]

Thank you..it worked!