SQL Pass Through LIKE conversion to SAS BASE

[metallon]

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!

[RW9]

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

[metallon]

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!

[Reeza]

|| is concatenation in SAS.

You probably want the find or index function instead.

[Ksharp · Accepted solution]

Using CONTAINS operator.

pd.your_id  contains  strip(ex.my_id) 

[Ksharp]

Using CONTAINS operator.

pd.your_id  contains  strip(ex.my_id)