Solved
Contributor
Posts: 66

remove duplicates based on two variables

hi,

I have several CSV files that contain correlations between two variables. These correlations were computed using twin modelling in R. The name of the two variables are in column depVar1 and depVar2. An example of duplication here is that the correlation between A and B duplicates the correlation between B and A:

• depVar1=A, depVar2=B
• depVar1=B, depVar2=A (this is a duplicate of the above)

To illustrate, I've groupped some rows by color and bold their duplicates as shown by the screenshot belows. Not all the duplicated rows are bold. However, values from column D to column K are the same in duplicated rows. I thought of removing duplicated rows by column D~ K. However, this is risky because different combinations of 2 variables can have same correlations. My data have been attached. There are 20 rows in the data. There should be 10 rows left after the removal of the duplicates. Thanks in advance.

Chang

Accepted Solutions
Solution
‎11-02-2016 03:28 AM
Super User
Posts: 10,699

Re: remove duplicates based on two variables

```
proc import datafile='c:\temp\_biVar_all_correlations_b.csv' out=have dbms=csv replace;
run;
data temp;
set have;
length new1 new2 \$ 40;
new1=depvar1;
new2=depvar2;
call sortc(new1,new2);
run;
proc sort data=temp out=want nodupkey;
by new1 new2;
run;

```

All Replies
Super User
Posts: 6,642

Re: remove duplicates based on two variables

If you always have the matching pair in your data, it should be easy to code:

if depvar1 > depvar2 then delete;

Solution
‎11-02-2016 03:28 AM
Super User
Posts: 10,699

Re: remove duplicates based on two variables

```
proc import datafile='c:\temp\_biVar_all_correlations_b.csv' out=have dbms=csv replace;
run;
data temp;
set have;
length new1 new2 \$ 40;
new1=depvar1;
new2=depvar2;
call sortc(new1,new2);
run;
proc sort data=temp out=want nodupkey;
by new1 new2;
run;

```
Contributor
Posts: 66

Re: remove duplicates based on two variables

Thanks. That's an elegant solution!

☑ This topic is solved.