DATA Step, Macro, Functions and more

LEFT JOIN - RETURN FIRST MATCHING ROW

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

LEFT JOIN - RETURN FIRST MATCHING ROW

[ Edited ]

Hi all,

 

I looking to pull geo data to a table via a left join. I have got to a point where I just want to join first matching result in my geo table to my original table. In oracle I would have used something like rownum < 2, is there an equivalent within SAS?

 

Below is an example of the join and conditions

 

LEFT JOIN
TMP_PRPN.COMPANIES_NACE_CODE_GEODIR2 t2
ON t1.join_status = 0
AND t1.geo_directory_grp = 'BUILDING'
AND t2.building_id = t1.geo_directory_id

AND t1.thorfare_id IS NOT MISSING
AND t2.thorfare_id = t1.thorfare_id
AND t2.nace_code LIKE ('Q.86.%')
AND prxchange('s/\b(DOCTOR)\b/ /o',-1,t2.organisation_name) =* cats(t1.firstname, t1.surname)

 

Any ideas?

 

Regards

Finbar


Accepted Solutions
Solution
‎12-08-2017 04:21 AM
Occasional Contributor
Posts: 7

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

I have created a work around where I have created a unique ID field in the left hand column. Done my left join where it has created extra rows.

 

After this I just done a Group By based on the unique id and selected the max address_reference field from the right hand table. Complete!

View solution in original post


All Replies
Super User
Super User
Posts: 9,402

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

If there is nothing in the data which indicates the record is the "first record" then why not just select distinct () on the values you want to use so you only get one record per group.  You can force it to take this adhoc "first record", however its not straight-forward, and not really a good way to be selecting data (as each time you run it, depending on sort orders, indexes, new/deleted data etc.) you may get a different result.  Better to isolate the data you really want logically that way each time you run it it will always work the same.

Occasional Contributor
Posts: 7

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

Building_ID is restricts to an individual building, so as I say for this analysis it will be perfectly fine. Could you send an example piece of code of how you would use it? I have populated fields within PL/SLQ using SELECT DISTINCT() but not in SAS.
Super User
Posts: 5,849

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

I assume it would be (very) similar.

Just search the PROC SQL documentation for DISTINCT.

Data never sleeps
Super User
Super User
Posts: 9,402

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

At a guess, the simplest method would be to add a sub-qeury:

LEFT JOIN
(select distinct BUILDING from TMP_PRPN.COMPANIES_NACE_CODE_GEODIR2) t2
ON t1.join_status = 0
AND t1.geo_directory_grp = 'BUILDING'
AND t2.building_id = t1.geo_directory_id
Occasional Contributor
Posts: 7

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

[ Edited ]

That does exactly the same as the left join on the full table. 

Super User
Super User
Posts: 9,402

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

You will note the word guess on the first line of the post here.  You have not provided anything for me to work with.  If you want a good answer provide some test data in the form of a datastep, post it in the code window (its the {I} above post area), and show what you want out.

You asked "SELECT DISTINCT() but not in SAS." - I showed how this works, I cannot guess your data or process or what you want.

Solution
‎12-08-2017 04:21 AM
Occasional Contributor
Posts: 7

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

I have created a work around where I have created a unique ID field in the left hand column. Done my left join where it has created extra rows.

 

After this I just done a Group By based on the unique id and selected the max address_reference field from the right hand table. Complete!

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 549 views
  • 2 likes
  • 3 in conversation