## Summing one-way costs to create two-way travel costs

Solved
Occasional Contributor
Posts: 10

# Summing one-way costs to create two-way travel costs

[ Edited ]

I have cost information that gives me the cost of transporting between locations, for example A and B, in both directions. For example, the cost of transportation from A to B is \$.05 and the cost of transportation from B to A is \$0.1. I would like to add these two costs, \$0.05 and \$0.1 to create round trip cost.

Year      Month   Company   Origin   Destination  Cost

2015     Jan        A5              CLE      CHI               0.05

2015     Jan        A5              CHI       CLE              0.1

2015     Jan        A6              COL      DCA             0.08

2015     Jan        A6              DCA      COL             0.11

I would like it to be:

Year      Month   Company   Origin   Destination  Cost

2015     Jan        A5              CLE      CHI               0.15

2015     Jan        A6              COL      DCA             0.19

The data lists only routes (one-way) and average costs.  I have considered sql, merge, modify. I am not sure which one would give the output I want without mistakes.

Accepted Solutions
Solution
4 weeks ago
Posts: 5,487

## Re: Summing one-way costs to create two-way travel costs

Get the round trip costs with:

``````data oneWayCosts;
input Year Month \$ Company \$ Origin \$ Destination \$ Cost;
datalines;
2015     Jan        A5              CLE      CHI               0.05
2015     Jan        A5              CHI       CLE              0.1
2015     Jan        A6              COL      DCA             0.08
2015     Jan        A6              DCA      COL             0.11
;

proc sql;
create table roundTripCosts as
select
a.year,
a.month,
a.company,
a.origin,
a.destination,
a.cost + b.cost as roundTripCost
from
oneWayCosts as a inner join
oneWayCosts as b
on a.year=b.year and a.month=b.month and a.company=b.company and
a.origin=b.destination and a.destination=b.origin
where a.origin < a.destination;
quit;``````
PG

All Replies
Super User
Posts: 23,357

## Re: Summing one-way costs to create two-way travel costs

Ok, so what do you need help with?

@larusso522 wrote:

I have cost information that gives me the cost of transporting between locations, for example A and B, in both directions. For example, the cost of transportation from A to B is \$.05 and the cost of transportation from B to A is \$0.1. I would like to add these two costs, \$0.05 and \$0.1 to create round trip cost.

Occasional Contributor
Posts: 10

## Re: Summing one-way costs to create two-way travel costs

How can I do that for 400000 rows of data that is listed by year, month, and company using SAS?

Super User
Posts: 23,357

## Re: Summing one-way costs to create two-way travel costs

That depends on how your data is arranged. Without knowing that it's impossible to answer your question. It could be a lookup problem - which I suspect it is.

Can you provide some mocked up data that reflects your problem, with the desired results?

If you can provide sample data and anything you've tried so far, you'll get a lot more responses, faster responses and code.

In general, vague question = vague answer.

Super Contributor
Posts: 266

## Re: Summing one-way costs to create two-way travel costs

[ Edited ]

Is this setup a reasonable facsimile of the problem?

``````* one way travel costs from point1 to point2;
data travel_cost;
input point1:\$1. point2:\$1. cost;
datalines;
a b .05
a c .75
a d .3
a e 5
b a 1
b d 2
b e 4
c a .56
c b 45
c d 1
c e 2
e a 1
e b .24
e c .01
e d 10
;
run;

* given an starting point and a destination point, what is round trip cost?;
* assume there can be multiple destinations;
* assume trips for costing are presented in a string format;
* a trip from A to B will be presented as AB;
* a trip from A to B to E will be presented as ABE;

data trips_to_calculate;
input trips \$.;
datalines;
AB
ABE
EBC
BC
;
run;

``````

??

PROC Star
Posts: 1,605

## Re: Summing one-way costs to create two-way travel costs

``````data have;
input Year      (Month   Company   Origin   Destination) (\$)  Cost;
cards;
2015     Jan        A5              CLE      CHI               0.05
2015     Jan        A5              CHI       CLE              0.1
2015     Jan        A6              COL      DCA             0.08
2015     Jan        A6              DCA      COL             0.11
;

data want;
if 0 then set have;
_cost=0;
do until(last.company);
set have;
by year month company;
_cost+cost;
end;
do _n_=1 by 1 until(last.company);
set have;
by year month company;
if _n_=1 then output;
end;
drop cost;
rename _cost=cost;
run;``````
PROC Star
Posts: 1,605

## Re: Summing one-way costs to create two-way travel costs

@larusso522 if every group are sets of two, consider merge have have(firstobs=2)  lookahead..There are tons of examples online and here. I am too lazy

PROC Star
Posts: 1,605

## Re: Summing one-way costs to create two-way travel costs

``````data have;
input Year      (Month   Company   Origin   Destination) (\$)  Cost;
cards;
2015     Jan        A5              CLE      CHI               0.05
2015     Jan        A5              CHI       CLE              0.1
2015     Jan        A6              COL      DCA             0.08
2015     Jan        A6              DCA      COL             0.11
;
data want;
set have have(in=b);
by year month company;
if first.company then do; _cost=0;_flag=0;end;
if not b then _cost+cost;
else if b then _flag+1;
if _flag=1;
drop cost _flag;
rename _cost=cost;
run;``````
Solution
4 weeks ago
Posts: 5,487

## Re: Summing one-way costs to create two-way travel costs

Get the round trip costs with:

``````data oneWayCosts;
input Year Month \$ Company \$ Origin \$ Destination \$ Cost;
datalines;
2015     Jan        A5              CLE      CHI               0.05
2015     Jan        A5              CHI       CLE              0.1
2015     Jan        A6              COL      DCA             0.08
2015     Jan        A6              DCA      COL             0.11
;

proc sql;
create table roundTripCosts as
select
a.year,
a.month,
a.company,
a.origin,
a.destination,
a.cost + b.cost as roundTripCost
from
oneWayCosts as a inner join
oneWayCosts as b
on a.year=b.year and a.month=b.month and a.company=b.company and
a.origin=b.destination and a.destination=b.origin
where a.origin < a.destination;
quit;``````
PG
Contributor
Posts: 30

## Re: Summing one-way costs to create two-way travel costs

HI can you make me understand  this please.

`````` a.origin=b.destination and a.destination=b.origin
where a.origin < a.destination;``````

Posts: 5,487

## Re: Summing one-way costs to create two-way travel costs

The query joins two copies of the cost table. The records that are joined have the origin from the first copy equal to the destination of the second, and vice-versa. The a.origin < a.destination condition is to prevent two instances of the same round trip from appearing: O -> D -> O and D -> O -> D.

PG
Occasional Contributor
Posts: 10

## Re: Summing one-way costs to create two-way travel costs

Do you know what happens if there are no matches? Does this program simply delete those?

Super User
Posts: 23,357

## Re: Summing one-way costs to create two-way travel costs

@larusso522 wrote:

Do you know what happens if there are no matches? Does this program simply delete those?

An inner join would delete any non matches. If you want everything from one table you can use a LEFT or RIGHT join.

Posts: 5,487

## Re: Summing one-way costs to create two-way travel costs

Yes one-ways are omitted from the results. If you wanted to signal their absence, you could use a full join:

``````data oneWayCosts;
input Year Month \$ Company \$ Origin \$ Destination \$ Cost;
datalines;
2015     Jan        A5              CLE      CHI               0.05
2015     Jan        A5              CHI       CLE              0.1
2015     Jan        A6              COL      DCA             0.08
2015     Jan        A6              DCA      COL             0.11
2016     Jan        A6              DCA      COL             0.11
;

proc sql;
create table roundTripCosts as
select
coalesce(a.year, b.year) as year,
coalesce(a.month, b.month) as month,
coalesce(a.company, b.company) as company,
coalesce(a.origin, b.destination) as origin,
coalesce(a.destination, b.origin) as destination,
a.cost + b.cost as roundTripCost
from
oneWayCosts as a full join
oneWayCosts as b
on a.year=b.year and a.month=b.month and a.company=b.company and
a.origin=b.destination and a.destination=b.origin
where calculated origin < calculated destination;
select * from roundTripCosts;
quit;
``````
PG
Super User
Posts: 10,700

## Re: Summing one-way costs to create two-way travel costs

``````data oneWayCosts;
input Year Month \$ Company \$ Origin \$ Destination \$ Cost;
datalines;
2015     Jan        A5              CLE      CHI               0.05
2015     Jan        A5              CHI       CLE              0.1
2015     Jan        A6              COL      DCA             0.08
2015     Jan        A6              DCA      COL             0.11
;
data temp;
set oneWayCosts;
call sortc(Origin , Destination);
run;

proc sql;
select Year, Month, Company,Origin , Destination,sum(Cost) as Cost
from temp
group by Year, Month, Company,Origin , Destination;
quit;``````
☑ This topic is solved.