BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TechRA
Calcite | Level 5
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.
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
Reeza
Super User

Can you post some sample data please. 

TechRA
Calcite | Level 5
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
PROC Star

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
Calcite | Level 5
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
PROC Star

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
Tourmaline | Level 20
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
TechRA
Calcite | Level 5
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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