topic Re: SQL Pass Through LIKE conversion to SAS BASE in SAS Programming

SQL Pass Through LIKE conversion to SAS BASE

metallon — Mon, 09 Feb 2015 09:20:33 GMT

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 — Mon, 09 Feb 2015 14:30:48 GMT

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

Reeza — Mon, 09 Feb 2015 15:20:42 GMT

|| is concatenation in SAS.

You probably want the find or index function instead.

metallon — Tue, 10 Feb 2015 11:23:28 GMT

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!

Ksharp — Tue, 10 Feb 2015 11:26:19 GMT

Using CONTAINS operator.

pd.your_id contains strip(ex.my_id)

Ksharp — Tue, 10 Feb 2015 11:28:56 GMT

Using CONTAINS operator.

pd.your_id contains strip(ex.my_id)