topic retaining unique combinations in SAS Data Management

retaining unique combinations

ilikesas — Thu, 30 Apr 2015 20:06:55 GMT

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!

Re: retaining unique combinations

morgalr — Thu, 30 Apr 2015 20:23:22 GMT

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.

Re: retaining unique combinations

Astounding — Thu, 30 Apr 2015 20:47:20 GMT

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.

Re: retaining unique combinations

data_null__ — Thu, 30 Apr 2015 20:53:13 GMT

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;

Re: retaining unique combinations

slchen — Thu, 30 Apr 2015 21:13:00 GMT

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;