@wtay wrote:
Assuming that the customer spends money when he/she is back in the home city, I am interested in finding a trip/series of trips in the period when he/she is away from the Home City. During this period, he/she may travel to multiple cities and each travel will be considered a trip.
Will
I will expand on my previous post.
First, sort your existing dataset by id and datetime.
Then run a data step
data want;
set have;
with
by cust_id trip_city notsorted;
(the notsorted option for trip_city is essential)
Retain a variable for start_datetime and a variable for total_amount:
retain start_datetime total_amount;
At first.trip_city, set the start_datetime and set the total_amount to zero:
if first.trip_city
then do;
start_datetime = datetime;
total_amount = 0;
end;
Add amd to total_amount.
total_amount + amt;
At last.trip_city, set the end_datetime and do an output.
if last.trip_city
then do;
end_datetime = datetime;
output;
end;
Keep only the variables needed:
keep id trip_city start_datetime end_datetime total_amount;
Additionally, end the data step:
run;
... View more