Duplicate check across columns

[TechRA]

I have a dataset that has multiple identifiers, and I want to check for duplicates across each of them. For example, each row has ID1, ID2, and ID3.

I want to know if ID1 from one row matches to ID2 or ID3 in another row (not within the same row). I can't think of an easy way to do this without restructuring the dataset. I'd prefer to do this check in SQL but can't figure out how. But I'd be satisfied with being able to do this check without restructuring. All I would want to do is identify the ID1 value that matches to ID2 or ID3 in another row.

Suggestions appreciated.

[Reeza]

Can you post some sample data please. 

[TechRA]

Sure here you go:

ID1 ID2 ID3 Gender
123 200 300 F
456 400 789 F
789 123 800 F
100 600 900 M

[Astounding · Accepted solution]

You may have to fix my syntax here, but this approach should work:

proc sql;

create table matches as select a.*, b.id2 as matching_id2, b.id3 as matching_id3 from 

have a, have b

where a.id1 ne b.id1 and (a.id1=b.id2 or a.id1=b.id3);

quit;

[TechRA]

Thank you. I hate to be too basic here but the last two lines are giving me errors on the syntax so I've pasted it below for correction. What am I doing wrong? Also could you explain the logic of what the having line is doing?

proc sql;

create table matches as select a.*, b.id2 as matching_id2, b.id3 as matching_id3 from datasetname

having a, having b

where a.id1 ne b.id1 and (a.id1=b.id2 or a.id1=b.id3);

quit;

[Astounding]

It looks like  you dropped the word "FROM" within the SELECT statement.

The word HAVE (or HAVING) is supposed to represent the name of your data set.  Most solutions that are posted here will use WANT as the name of the data set you want to obtain, and HAVE as the name of the data set you are starting with.  So just replace that with the name of your data set.

[LinusH]

To me the data structure looks awkward. Shouldn't the data be transposed? Then it would much easier to keep track on duplicates and other processing as well.

[TechRA]

Thanks for the help all! Took me a little bit to figure out the unexpected results which was primarily due not using an additional identifier for this check.

Also I agree that transposing is the simplest option. However, the ultimate goal was to keep the dataset at one observation per person so I thought it might be more efficient to keep it flat rather than restructure and reflatten.