topic Re: remove duplicates based on two variables in SAS Programming

remove duplicates based on two variables

Chang — Mon, 31 Oct 2016 14:43:45 GMT

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

Re: remove duplicates based on two variables

Astounding — Mon, 31 Oct 2016 14:49:51 GMT

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

if depvar1 > depvar2 then delete;

Re: remove duplicates based on two variables

Ksharp — Tue, 01 Nov 2016 04:42:13 GMT


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;

Re: remove duplicates based on two variables

Chang — Wed, 02 Nov 2016 07:28:36 GMT

Thanks. That's an elegant solution!