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.
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.
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
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.