DATA Step, Macro, Functions and more

remove duplicates based on two variables

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

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.

 

duplicates_in_bivariate_modelling.png

 

Chang

Attachment

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

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;


View solution in original post


All Replies
Super User
Posts: 5,071

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: 9,662

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: 61

Re: remove duplicates based on two variables

Thanks. That's an elegant solution!

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 467 views
  • 1 like
  • 3 in conversation