Not an error in way shape or form, it is a designed and useful feature of SQL. Any of the JOINs with duplicate values of ON variables will by default create a combination of the two records.
A small example that you can run to see what what happens IF you are using SAS. The small number of records, variables and unique values below make it pretty easy to see that where X is duplicated in both set One and Two that the Join matches all the records from One to all of the matches in Two. This behavior is pretty much a standard in all of the SQL variants (and you aren't using SAS Proc SQL but possibly Oracle?)
data one;
input x y ;
datalines;
1 1
1 2
1 3
2 4
;
data two;
input x z ;
datalines;
1 100
1 200
2 6
;
proc sql;
create table example as
select a.x, a.y, b.z
from one as a
left join
two as b
on a.x=b.x
;
quit;
Solution: If your results are truly actual duplicates, as in every single resulting variable you could try adding the DISTINCT predicate as the start of the Select. Caution: there can be a lot of computational overhead with this instruction. That query already has a number of potential execution time flags with multiple subqueries in different places.
Other wise you need to reduce the duplicates earlier which could mean breaking that query up into smaller pieces.
... View more
