SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

retaining unique combinations

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

retaining unique combinations

hi,

suppose that I have the following table:

com1com2year
AB2000
AC2001
AD2002
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
AD2002
BF1999
CK1998

thank you!


Accepted Solutions
Solution
‎04-30-2015 04:53 PM
Respected Advisor
Posts: 3,777

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 comSmiley Happy;
   run;
proc print;
  
run;
proc summary data=scom nway;
  
class com: year;
   output out=unique(drop=_type_);
   run;
proc print;
  
run;


4-30-2015 3-52-22 PM.png

View solution in original post


All Replies
Contributor
Posts: 26

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: 5,099

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
Respected Advisor
Posts: 3,777

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 comSmiley Happy;
   run;
proc print;
  
run;
proc summary data=scom nway;
  
class com: year;
   output out=unique(drop=_type_);
   run;
proc print;
  
run;


4-30-2015 3-52-22 PM.png
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.

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

Discussion stats
  • 4 replies
  • 341 views
  • 4 likes
  • 5 in conversation