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
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!
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.
I assume it would be (very) similar.
Just search the PROC SQL documentation for DISTINCT.
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
That does exactly the same as the left join on the full table.
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.
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.