topic Re: SQL PROC SORT producing duplicates in SAS Programming

SQL PROC SORT producing duplicates

iliyan — Wed, 14 Feb 2018 00:30:37 GMT

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.

Re: SQL PROC SORT producing duplicates

art297 — Wed, 14 Feb 2018 01:02:57 GMT

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

Re: SQL PROC SORT producing duplicates

iliyan — Wed, 14 Feb 2018 01:25:51 GMT

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

Re: SQL PROC SORT producing duplicates

art297 — Wed, 14 Feb 2018 01:36:01 GMT

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

Re: SQL PROC SORT producing duplicates

iliyan — Wed, 14 Feb 2018 03:42:11 GMT

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.