Pairing two variable values.

[VX_Xc]

Say I have a dataset below;

country1 country2 transactions

Korea     USA       100

USA       Korea     100

I would like to make a new variable with one paired value and sum transactions together, that is I want

countrypair   Total_trans

Korea-USA   200

So that Korea USA pair is the same as USA Korea pair. 

I have 40 or so countries, and have to pair them like the example above.

Is there anyway to do this other than having to specify each country?

Thanks for your help

[art297 · Accepted solution]

data have (keep=country transactions);

  input country1 $ country2 $ transactions;

  array cty(*) coun:;

  call sortc(of cty(*));

  country=catx('-',of cty(*));

  cards;

Korea     USA       100

USA       Korea     100

;

[Ksharp]

Arthur,

Very good.

Ksharp

[Linlin]

data have (keep=country transactions);

  input country1 $ country2 $ transactions;

  array cty(*) coun:;

  call sortc(of cty(*));

  country=upcase(catx('-',of cty(*)));

  cards;

Korea     USA       100

USA       Korea     100

China     Canada    200

Canada    China     300

;

proc sql;

  create table want as

    select country,sum(transactions) as total

             from have

               group by country;

quit;

proc print;run;

                                   Obs      country       total

                                     1     CANADA-CHINA     500

                                     2     KOREA-USA           200

[PGStats]

data have;
input country1 $ country2 $ transactions;
datalines;
Korea     USA       100
USA       Korea     100
;

proc sql;
create table want as
select case when country1<country2 then catx("-",country1,country2) else catx("-",country2,country1) end as countryPair,
sum(transactions) as trade
from have
group by calculated countryPair;

proc print; run;

PG