DATA Step, Macro, Functions and more

SQL PROC SORT producing duplicates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

SQL PROC SORT producing duplicates

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.


Accepted Solutions
Solution
‎02-13-2018 10:42 PM
Occasional Contributor
Posts: 13

Re: SQL PROC SORT producing duplicates

[ Edited ]

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.

View solution in original post


All Replies
Super User
Posts: 8,216

Re: SQL PROC SORT producing duplicates

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

 

Occasional Contributor
Posts: 13

Re: SQL PROC SORT producing duplicates

I want the lhrs in the mvab file to be bounded between the to and from lhrs
of the pavement file.
Super User
Posts: 8,216

Re: SQL PROC SORT producing duplicates

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

 

Solution
‎02-13-2018 10:42 PM
Occasional Contributor
Posts: 13

Re: SQL PROC SORT producing duplicates

[ Edited ]

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 115 views
  • 0 likes
  • 2 in conversation