Help using Base SAS procedures

Pairing two variable values.

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Pairing two variable values.

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


Accepted Solutions
Solution
‎02-25-2012 11:51 PM
PROC Star
Posts: 7,489

Re: Pairing two variable values.

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

;

View solution in original post


All Replies
Solution
‎02-25-2012 11:51 PM
PROC Star
Posts: 7,489

Re: Pairing two variable values.

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

;

Super User
Posts: 10,044

Re: Pairing two variable values.

Arthur,

Very good.

Ksharp

Super Contributor
Posts: 1,636

Re: Pairing two variable values.

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

Respected Advisor
Posts: 4,930

Re: Pairing two variable values.

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

PG
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 195 views
  • 10 likes
  • 5 in conversation