SAS - sub select with like

Question

hope everyone had a good weekend.

I have a table (tableA) with one column that has values such as the below:

Column1:

xyz-12345678
rdr-32343234
fgm-23423423

Then i have another table (tableB) with one column that has all the values in tableA.column1 and then some more such as the below

column1:

rfxyz-1234567800012015-01-029
grrdr-3234323485832015-02-037
tyfgm-2342342343432014-12-148

As you can see tableA.column1 has its value embedded in tableB.column1. I wrote a subselect query such as below to identify all instances in tableB.column1 that have tableA.column1 values embedded in them, but i get 0 results and no errors. I can clearly see that there are row values in tableA.column1 that are present in tableB.column1 but i am unsure what i have done wrong here to result in no errors and no results, my SAS proc sql is below:

PROC SQL;
select i.*
from tableA i
where exists (select *
              from tableB
              where i.column1 like '%'||column1||'%'
             )
;
quit;

Any help on this would be greatly appreciated as my SAS knowledge is not really strong.

score 3 · Accepted Answer · answered Jun 01 '15 at 14:54

3

Your condition is backwards. TableB has the longer column, so you want it on the left side of LIKE:

PROC SQL;
select i.*
from tableA i
where exists (select *
              from tableB b
              where b.column1 like '%' || i.column1 || '%'
             )
;
quit;

I encourage you to use table aliases for all your column references, especially in correlated subqueries.

answered Jun 01 '15 at 14:54

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon, i will give this a whirl and let you know. And yes I will use aliases as you recommended. – vbala2014 Jun 01 '15 at 14:59
Thank you again Gordon, this worked for me. I had to put a trim(i.column1) in there at the end in the subselect where clause because of extra space characters, but this worked and i got some results back. Giving your answere the green check and +1 for the right solution. – vbala2014 Jun 01 '15 at 15:02

score 1 · Answer 2 · answered Jun 01 '15 at 14:56

Your attempt and your description don't quite match up. If your attempt is to be believed you are looking for values in tableA, where the value is contianed in another record in tableB. If that is the case then you just have your where clause slightly mixed up, it should be:

select i.*
from tableA i
where exists (select *
              from tableB
              where tableB.column1 like '%'||i.column1||'%'
             );

Substituting in values what you had before was:

WHERE 'xyz-12345678' LIKE '%tyfgm-2342342343432014-12-148%'

Which is false, you need it to be:

WHERE 'tyfgm-2342342343432014-12-148' LIKE '%xyz-12345678%'

If your description is to be believed, then your where clause is right, but your tables are the wrong way round:

to identify all instances in tableB.column1 that have tableA.column1 values embedded in them

So you would want to select from tableB:

select i.*
from tableB i
where exists (select *
              from tableA a
              where i.column1 like '%'||a.column1||'%'
             );

thanks Gareth for the input. I have realized the error in my ordering and the query now works. Thanks to you and Gordon for your inputs, much appreciated — vbala2014, Jun 01 '15 at 15:22

score 1 · Answer 3 · answered Jun 01 '15 at 15:19

In addition to the issues noted in the other answers, I would suggest a basic technique to avoid issues with spaces.

where i.column1 like cats('%',a.column1,'%')

cats automatically strips spaces from both sides. There is also cat (just concatenate), catt (just trim from the right), and catx/catq (delimit, the latter quotes also similar to dsd).

These are much more readable than a bunch of nested functions, and avoid issues like this which come up when you forget to trim, strip, etc.

SAS - sub select with like

3 Answers3