BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
iliyan
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
iliyan
Obsidian | Level 7

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

4 REPLIES 4
art297
Opal | Level 21

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
Obsidian | Level 7
I want the lhrs in the mvab file to be bounded between the to and from lhrs
of the pavement file.
art297
Opal | Level 21

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
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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