retaining unique combinations

[ilikesas]

hi,

suppose that I have the following table:

com1	com2	year
A	B	2000
A	C	2001
A	D	2002
B	A	2000
B	F	1999
C	A	2001
C	K	1998

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:

com1	com2	year
A	B	2000
A	C	2001
A	D	2002
B	F	1999
C	K	1998

thank you!

[morgalr]

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.

[Astounding]

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.

[data_null__ · Accepted solution]

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;

[slchen]

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;