Help using Base SAS procedures

Duplicate check across columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Duplicate check across columns

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.

Accepted Solutions
Solution
‎05-25-2017 06:23 PM
Super User
Posts: 5,081

Re: Duplicate check across columns

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;

View solution in original post


All Replies
Super User
Posts: 17,819

Re: Duplicate check across columns

Can you post some sample data please. 

Occasional Contributor
Posts: 6

Re: Duplicate check across columns

Sure here you go:

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

Solution
‎05-25-2017 06:23 PM
Super User
Posts: 5,081

Re: Duplicate check across columns

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;

Occasional Contributor
Posts: 6

Re: Duplicate check across columns

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;

Super User
Posts: 5,081

Re: Duplicate check across columns

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.

Super User
Posts: 5,256

Re: Duplicate check across columns

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.
Data never sleeps
Occasional Contributor
Posts: 6

Re: Duplicate check across columns

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.
☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 199 views
  • 0 likes
  • 4 in conversation