topic Re: LEFT JOIN - RETURN FIRST MATCHING ROW in SAS Programming

LEFT JOIN - RETURN FIRST MATCHING ROW

fgillen1 — Thu, 07 Dec 2017 15:04:40 GMT

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

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

RW9 — Thu, 07 Dec 2017 15:22:53 GMT

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.

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

fgillen1 — Thu, 07 Dec 2017 15:34:53 GMT

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.

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

LinusH — Thu, 07 Dec 2017 15:37:38 GMT

I assume it would be (very) similar.

Just search the PROC SQL documentation for DISTINCT.

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

RW9 — Thu, 07 Dec 2017 15:37:48 GMT

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

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

fgillen1 — Thu, 07 Dec 2017 15:52:14 GMT

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

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

RW9 — Fri, 08 Dec 2017 09:14:17 GMT

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.

Re: LEFT JOIN - RETURN FIRST MATCHING ROW

fgillen1 — Fri, 08 Dec 2017 09:19:36 GMT

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!