DATA Step, Macro, Functions and more

SQL Pass Through LIKE conversion to SAS BASE

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

SQL Pass Through LIKE conversion to SAS BASE

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!


Accepted Solutions
Solution
‎02-10-2015 06:26 AM
Super User
Posts: 10,041

Re: SQL Pass Through LIKE conversion to SAS BASE

Using CONTAINS operator.

pd.your_id  contains  strip(ex.my_id

View solution in original post


All Replies
Super User
Super User
Posts: 7,977

Re: SQL Pass Through LIKE conversion to SAS BASE

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

Frequent Contributor
Posts: 133

Re: SQL Pass Through LIKE conversion to SAS BASE

Hi RW9 and Reeza,

below some test data:

     ex.my_ID              pd.your_ID

15LFM012615LFM0126-01trueb contains a
15LFM0126-0115LFM0126truea contains b
15LFM0126-01321AFfalseno match at all
1552211MK   1931552211MK 193truea 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!

Super User
Posts: 19,835

Re: SQL Pass Through LIKE conversion to SAS BASE

|| is concatenation in SAS.

You probably want the find or index function instead.

Solution
‎02-10-2015 06:26 AM
Super User
Posts: 10,041

Re: SQL Pass Through LIKE conversion to SAS BASE

Using CONTAINS operator.

pd.your_id  contains  strip(ex.my_id

Super User
Posts: 10,041

Re: SQL Pass Through LIKE conversion to SAS BASE

Using CONTAINS operator.

pd.your_id  contains  strip(ex.my_id

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 287 views
  • 3 likes
  • 4 in conversation