BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metallon
Pyrite | Level 9

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Using CONTAINS operator.

pd.your_id  contains  strip(ex.my_id

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9

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!

Reeza
Super User

|| is concatenation in SAS.

You probably want the find or index function instead.

Ksharp
Super User

Using CONTAINS operator.

pd.your_id  contains  strip(ex.my_id

Ksharp
Super User

Using CONTAINS operator.

pd.your_id  contains  strip(ex.my_id

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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