Combining Like Origin and Destination Types

[BCNAV]

I have airline origin and destination data that looks like:

Departure   Destination    Current Count    Previous Count

CYEG        CYYZ           51               22

CYYZ        CYEG           53               67

RPLL        CYYZ           21                6

CYVR        CYYC           11                9

CYYC        CYVR           15                1

What really matters is the total traffic between COMMON city pairs. What would be good would be to get output as:

Common City Pair   Current Count   Previous Count

CYEG <--> CYYZ     104             89

RPLL <--> CYYZ      21              6

CYVR <--> CYYC      26             10

The values in the Current Count and Previous count are now the sums of the COMMON city pairs in the original data. Common city pairs can be anywhere in the dataset and not necessarily right after one another.

Thanks!

[Reeza · Accepted solution]

Create two new variables and use CALL SORTC() on them to sort the two variables and then summarize on those.


data temp;
set have;
loc1 = origin;
loc2 = destination;
call sortc(loc1, loc2);
run;

proc means data=temp NWAY;
class loc1 loc2;
var current_count prev_count;
output out=want sum= /autoname;
run;

[BCNAV]

thanks!!