SQL PROC SORT producing duplicates

[iliyan]

Hello everyone,

I am trying to bring together two tables using the following code:

proc sql;
      create table my.mvabpavement2008 as
      select *
      from  my.mvab2008p mvab, my.pavement2008 pave
where mvab.dirtra = pave.direction and mvab.REFERENCE_NO >= pave.from_lhrs and pave.to_lhrs >= mvab.REFERENCE_NO;
quit;

However, when running that code I get duplicate results especially in cases where REFERENCE_NO= from_lhrs and REFERENCE_NO (not)equal to_lhrs and the vice versa. I would think that this paraphrasing would indicate that unless statements are satisfied it would not produce an observation. What am I doing wrong?

A sample is attached.

[art297]

Why do you think they are duplicates? Your're merging two files and your conditions on those variables are >=, not equal to.

Art, CEO, AnalystFinder.com

[iliyan]

I want the lhrs in the mvab file to be bounded between the to and from lhrs
of the pavement file.

[art297]

Are you saying that you want:

where mvab.dirtra = pave.direction and (pave.from_lhrs <= mvab.REFERENCE_NO <=  and pave.to_lhrs)
;

of course, that is if mvab.REFERENCE_NO represents the lhrs to said existed in the mvab file.

Art, CEO, AnalystFinder.com

[iliyan · Accepted solution]

EDIT, The solution is:

where mvab.dirtra = pave.direction and (pave.from_lhrs <= mvab.REFERENCE_NO < pave.to_lhrs);

All that had to be done is to restrict the to_LHRS from matching.