topic Re: SQL query returns duplicates in SAS Programming

SQL query returns duplicates

Ryanb2 — Fri, 14 Aug 2020 17:42:20 GMT

I'm querying an Oracle database and need to use SQL. I'm trying to pull records from the oracle database where the year of birth is either 2019 or 2020 AND (match either DOB OR first name OR last name OR mother's last) from records in a SAS dataset. When I do this I get duplicate records (in the millions) when I'd like to get only one distinct record even when the same record meets the where criteria for multiple records in the SAS dataset. Any help is appreciated.

proc sql;
	create table OUTPUT as
	select 
		a.ID as CERTIFICATEID label='CERTIFICATEID', a.BIRTH_ID, 
		a.SEALED, a.CREATE_DATE,
		datepart(a.BIRTH_DATE) as B_DOB label='B_DOB' format=mmddyy10., a.CHILD_SEX as B_SEX label='B_SEX', 
		a.CHILD_NAME_FIRST as B_NAME_FIRST label='B_NAME_FIRST', a.CHILD_NAME_MIDDLE as B_NAME_MIDDLE label='B_NAME_MIDDLE', a.CHILD_NAME_LAST as B_NAME_LAST label='B_NAME_LAST', 
		a.PARENT_1_NAME_FIRST as B_NAME_FIRST_FATHER label='B_NAME_FIRST_FATHER', a.PARENT_1_NAME_MIDDLE as B_NAME_MIDDLE_FATHER label='B_NAME_MIDDLE_FATHER', a.PARENT_1_NAME_LAST as B_NAME_LAST_FATHER label='B_NAME_LAST_FATHER', 
		a.PARENT_2_NAME_FIRST as B_NAME_FIRST_MOTHER label='B_NAME_FIRST_MOTHER', a.PARENT_2_NAME_MIDDLE as B_NAME_MIDDLE_MOTHER label='B_NAME_MIDDLE_MOTHER', a.PARENT_2_NAME_LAST as B_NAME_LAST_MOTHER label='B_NAME_LAST_MOTHER'
	from ORACLE.TABLE as a, SAS_DATASET as e
	where 
		year(datepart(a.BIRTH_DATE)) in (2020 2019) /* Do not include if not state registered*/
		AND
		(
		a.CHILD_NAME_FIRST=e.D_NAME_FIRST OR
		a.CHILD_NAME_LAST=e.D_NAME_LAST OR
		put(datepart(a.BIRTH_DATE),mmddyy10.)=e.D_DOB OR
		a.PARENT_2_NAME_FIRST=e.D_NAME_FIRST_MOTHER
		)
	;
quit;

Re: SQL query returns duplicates

Reeza — Fri, 14 Aug 2020 17:55:05 GMT

The OR condition is likely making it possible to join to the same row multiple times....I suspect you'll need to either modify that join condition somehow or do it in multiple steps to avoid duplicates and matching records that are already matched.
You are also using a cross join which is inefficient.

Re: SQL query returns duplicates

KathyKiraly — Fri, 14 Aug 2020 19:29:54 GMT

Try the DISTINCT keyword in your SELECT for unique rows across all columns:

select distinct
a.ID as CERTIFICATEID label='CERTIFICATEID', a.BIRTH_ID,
a.SEALED, a.CREATE_DATE,
...

Re: SQL query returns duplicates

Ryanb2 — Fri, 14 Aug 2020 20:33:30 GMT

That did it! Thanks. I also changed it to a left join but kept the where statement as is. Much appreciated.

Re: SQL query returns duplicates

Reeza — Fri, 14 Aug 2020 20:34:48 GMT

You need to mark the solution as correct, not your response.

Re: SQL query returns duplicates

Ryanb2 — Fri, 14 Aug 2020 20:50:16 GMT

Oops! Sorry. How do correct this?