Hi SAS Friends,
I have a problem converting the below SQL statement (that works fine using SQL PASS THROUGH in BASE SAS.
The data is now stored in SAS data sets and no longer on the DB hence I need a new join that works with the LIKE flexibility that I previously
utilized in SQL.
SELECT my_ID,PD.your_ID
FROM testdata2L ex
LEFT JOIN testdata2.pd
ON pd.your_id like '%'||ex.my_id||'%' OR ex.my_id like '%'||pd.your_id||'%'
when I use the below as proc sql without SQL PASS THROUGH the LIKE logic does NOT work...somehow. It only works with SQL PASS THROUGH.
Thanks for any help!
I think your mixing your concatenations up there. Show some test data of what you have and what you want. You could of course re-write to:
SELECT my_ID,PD.your_ID
FROM testdata2L ex
LEFT JOIN testdata2.pd
ON index(pd.your_id,ex.my_id) > 0
OR index(ex.my_id,pd.your_id) > 0
I.e. your_id is found in my_idf my_id, or my_id is found in your_id
Hi RW9 and Reeza,
below some test data:
ex.my_ID pd.your_ID
15LFM0126 | 15LFM0126-01 | true | b contains a |
15LFM0126-01 | 15LFM0126 | true | a contains b |
15LFM0126-01 | 321AF | false | no match at all |
1552211MK 193 | 1552211MK 193 | true | a and be are the same apart from the space |
the index function did not work. I got a regex that does the trick:
regexp_substr(REPLACE(pd.your_ID,' ',''),'[^-]+',1,1)
but again...only with SQL PASS THROUGH. non of that works in SAS BASE. neither the INDEX, the PUT for converion or the LIKE or the REGEX.
@RW9, yes I probably mix up syntax...because no native SAS syntax
is working so far. @Reeza, double pipe is concatenation in ORACLE SQL!
|| is concatenation in SAS.
You probably want the find or index function instead.
Using CONTAINS operator.
pd.your_id contains strip(ex.my_id)
Using CONTAINS operator.
pd.your_id contains strip(ex.my_id)
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.