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!
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.
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 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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.