BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

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!

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19
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;


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

View solution in original post

4 REPLIES 4
morgalr
Obsidian | Level 7

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
PROC Star

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__
Jade | Level 19
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;


4-30-2015 3-52-22 PM.png
slchen
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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