topic Re: Summing one-way costs to create two-way travel costs in SAS Programming

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

larusso522 — Tue, 29 May 2018 17:58:48 GMT

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.

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

Reeza — Tue, 29 May 2018 16:39:00 GMT

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.

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

larusso522 — Tue, 29 May 2018 17:31:09 GMT

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

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

Reeza — Tue, 29 May 2018 17:34:44 GMT

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.

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

HB — Tue, 29 May 2018 17:40:41 GMT

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 trip always ends with a return to start;
* 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;

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

novinosrin — Tue, 29 May 2018 18:09:05 GMT

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;

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

novinosrin — Tue, 29 May 2018 18:10:44 GMT

@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

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

novinosrin — Tue, 29 May 2018 18:15:16 GMT

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;

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

PGStats — Tue, 29 May 2018 18:24:11 GMT

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;

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

Ksharp — Wed, 30 May 2018 13:38:22 GMT

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;

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

SJN — Wed, 30 May 2018 13:48:41 GMT

HI can you make me understand this please.

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

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

PGStats — Wed, 30 May 2018 20:46:36 GMT

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.

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

larusso522 — Wed, 06 Jun 2018 00:47:20 GMT

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

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

Reeza — Wed, 06 Jun 2018 02:56:54 GMT

@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.

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

PGStats — Wed, 06 Jun 2018 03:05:20 GMT

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;