topic Question about filtering data - please help! in SAS Enterprise Guide

Question about filtering data - please help!

Anya — Wed, 19 Oct 2011 14:38:10 GMT

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

Question about filtering data - please help!

art297 — Wed, 19 Oct 2011 15:22:23 GMT

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?

Question about filtering data - please help!

Anya — Wed, 19 Oct 2011 15:32:50 GMT

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

Question about filtering data - please help!

art297 — Wed, 19 Oct 2011 16:31:48 GMT

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;

Question about filtering data - please help!

Anya — Thu, 20 Oct 2011 13:57:43 GMT

Thank you..it worked!