## retaining unique combinations

Solved
Super Contributor
Posts: 459

# retaining unique combinations

hi,

suppose that I have the following table:

com1com2year
AB2000
AC2001
BA2000
BF1999
CA2001
CK1998

please note that the combinations A-B and A-C are present 2 times each, so what I would like to do is to include each combination only once and get the following table:

com1com2year
AB2000
AC2001
BF1999
CK1998

thank you!

Accepted Solutions
Solution
‎04-30-2015 04:53 PM
Posts: 3,847

## Re: retaining unique combinations

data com;
input (com1  com2)(:\$1.)  year;
cards;
A  B  2000
A  C  2001
A  D  2002
B  A  2000
B  F  1999
C  A  2001
C  K  1998
;;;;
run;
data scom;
set com;
call sortc(of com;
run;
proc print;

run;
proc summary data=scom nway;

class com: year;
output out=unique(drop=_type_);
run;
proc print;

run;

All Replies
Contributor
Posts: 34

## Re: retaining unique combinations

The only way I know of is the manual way, which is probably what you are trying to avoid. The fastest way I know of is to insert all combinations into a "already used table", then do a lookup on it.

Super User
Posts: 6,637

## Re: retaining unique combinations

Switching data values around would work:

data halfway_there;

set have;

if com1 > com2 then do;

dummy = com1;

com1 = com2;

com2 = dummy;

end;

drop dummy;

run;

proc sort data=halfway_there out=want nodupkey;

by com1 com2;

run;

Possibly you want the final sort BY COM1 COM2 YEAR, but that is up to you.

I would bet the SQL programmers out there can do it in one step, along the lines of:

min(com1, com2) as com1, max(com1, com2) as com2

in combination with SELECT DISTINCT.  I know the experiments I would try, but I'm not well versed enough to write out the code off the top of my head.

Good luck.

Solution
‎04-30-2015 04:53 PM
Posts: 3,847

## Re: retaining unique combinations

data com;
input (com1  com2)(:\$1.)  year;
cards;
A  B  2000
A  C  2001
A  D  2002
B  A  2000
B  F  1999
C  A  2001
C  K  1998
;;;;
run;
data scom;
set com;
call sortc(of com;
run;
proc print;

run;
proc summary data=scom nway;

class com: year;
output out=unique(drop=_type_);
run;
proc print;

run;

Super Contributor
Posts: 275

## Re: retaining unique combinations

data want;

set have;

length string \$200.;

retain string;

if index(string,catx('-',com1,com2))=0 then output;

string=catx(',',string,catx('-',com2,com1));

drop string;

run;

🔒 This topic is solved and locked.