topic Re: Programming question in SAS Programming

Programming question

SAS42 — Tue, 30 Aug 2016 19:50:25 GMT

I have a travel dataset as follows:

travelerid orig dest traveltime time_spent_at_dest orig_stop dest_stop

1 A B 5 0 Y N

1 B C 2 2 N N

1 C D 3 1 N Y

1 D E 2 1 Y Y

...........

And I want to sum up the time spent between any two consecutive stopping stations

(not counting the time spent at the current stopping station),

for example, the output dataset should look like:

travlerid orig dest traveltime

1 A D 12

1 D E 2

...........

Can anyone help me with this question?

Thanks!

Re: Programming question

Astounding — Tue, 30 Aug 2016 20:35:42 GMT

Make sure you stash the original data somewhere safe ... if you ever get this data out of order it will be a monstrous task to reassemble it. You would be better off creating new fields (TRIP_ID, LEG) that would let you put the data back in order if a problem ever arose.

All that being said, here's an approach you can try for your problem:

data want;

set have;

if orig_stop='Y' then do;

total_time=0;

starting_point = orig;

end;

retain total_time starting_point;

if dest_stop = 'N' then total_time = total_time + traveltime + time_spent_at_dest;

else do;

total_time = total_time + traveltime;

ending_point = dest;

output;

end;

keep travelerid starting_point ending_point total_time;

run;

It's untested code, but should be OK.

Re: Programming question

ballardw — Tue, 30 Aug 2016 20:41:42 GMT

Here's one way:

data want (keep=travelerid orig dest traveltime);
   set have;
   retain cumtime 0 RealOrig "   ";
   if orig_stop='Y' then do;
      cumtime=0;
      RealOrig=Orig;
   end;
   If dest_stop = 'N' then cumtime= sum(cumtime,traveltime,time_spent_at_dest);
   if dest_stop='Y' then do;
      cumtime= sum(cumtime,traveltime);
      orig= RealOrig;
      TravelTime=CumTime;
      output;
   end;
run;

This assumes well formed data: No traveller id starting with something for Orig_stop other than 'Y', last traveller Id is Dest_stop='Y'.

If you travell id doesn't behave that way then you'll need to make some decisions about how to handle the exceptions. They might be amenable to BY Travellerid processing with First and Last but no promises.