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)
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.
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.