topic Re: How to compare values in two columns regardless of the row orders? in SAS Programming

How to compare values in two columns regardless of the row orders?

almmotamedi — Mon, 22 Feb 2016 22:13:31 GMT

Hi SAS users,

I have two columns with the same type character values. I just want to check if there is any duplicate between thease two columns (when I say duplicate, I don't mean duplicate at each row; I mean any repeated value in both columns regardless of the order.) Could you please help with it?

Thank you

Re: How to compare values in two columns regardless of the row orders?

Reeza — Mon, 22 Feb 2016 22:50:51 GMT

What do you want the output to be?

Re: How to compare values in two columns regardless of the row orders?

FreelanceReinh — Mon, 22 Feb 2016 23:08:52 GMT

Hi @almmotamedi,

Do you mean something like this?

data have;
length var1 var2 $10;
input var1 var2;
cards;
blue   red
green  yellow
red    brown
black  green
;

proc sql;
create table dups as
select a.var1
from have a, have b
where a.var1=b.var2;
quit;

proc print noobs;
run;

Result:

var1

green
red

Re: How to compare values in two columns regardless of the row orders?

mohamed_zaki — Mon, 22 Feb 2016 23:35:46 GMT

You may need to

select distinct a.var1

if the value could be repeated in each column more than one time. Or you will end up by long list.

Re: How to compare values in two columns regardless of the row orders?

Ksharp — Tue, 23 Feb 2016 01:29:44 GMT

It looks like you need some QUERY tool. MERGE, Hash Table, SQL , IML ,Array ..... all can do that.

Here is the IML code :

data have;
length var1 var2 $10;
input var1 var2;
cards;
blue   red
green  yellow
red    brown
black  green
;
run;
proc iml;
use have;
read all var {var1 var2};
close have;
x=var1[loc(element(var1,var2))];
print x;
quit;

Re: How to compare values in two columns regardless of the row orders?

almmotamedi — Tue, 23 Feb 2016 03:11:52 GMT

Its simply like this:

column1 column2

A B

B C

I would like to create the New_column1 as below:

New_column1

(Because, A and D are not repeated/duplicate in the 2nd column.)

Re: How to compare values in two columns regardless of the row orders?

FreelanceReinh — Tue, 23 Feb 2016 09:13:27 GMT

Hi @almmotamedi,

Thanks for the clarification. If you want to create a new dataset (call it WANT) with one variable, New_column1, from the existing dataset (call it HAVE) with the two variables, you could use the EXCEPT operator in PROC SQL:

proc sql;
create table want as
select column1 as New_column1 from have
except
select column2 from have;
quit;

This assumes that, say, "B" should not occur in New_column1 even if it occurred twice in column1, but only once in column2. If, in the latter case, you would like to take into account the "multiplicities", i.e. eliminate only one of the two occurrences of "B", just replace except by except all in the code above.

Re: How to compare values in two columns regardless of the row orders?

almmotamedi — Tue, 23 Feb 2016 14:07:47 GMT

Thank you so much