I want to join 2 tables on the condition that variable X from Table 1
appears in a comma separated list character variable in Table 2.

What is the best way to approach this?

I was trying to use REGEX in something like this:

PROC SQL;
	CREATE TABLE test AS
	SELECT  *
	FROM a LEFT JOIN b
	ON prxmatch("/(^a.id)|(a.id$)|(,a.id,)/", STRIP(b.id_list));
QUIT;

But I don't think there is anyway to reference the variable a.id from
within the Regular Expression.

The find function WOULD have worked except, in this case, ids are not
fixed length.  (So there might be some unwanted matches to ids that
appear within other ids)

Any help is appreciated :)

What about the FINDW function?

where findw(b.id_list,a.id,',')


On Jan 8, 5:14=A0pm, Praveen Sawh <praveens...@gmail.com> wrote:
> I want to join 2 tables on the condition that variable X from Table 1
> appears in a comma separated list character variable in Table 2.
>
> What is the best way to approach this?
>
> I was trying to use REGEX in something like this:
>
> PROC SQL;
> =A0 =A0 =A0 =A0 CREATE TABLE test AS
> =A0 =A0 =A0 =A0 SELECT =A0*
> =A0 =A0 =A0 =A0 FROM a LEFT JOIN b
> =A0 =A0 =A0 =A0 ON prxmatch("/(^a.id)|(a.id$)|(,a.id,)/", STRIP(b.id_list=
));
> QUIT;
>
> But I don't think there is anyway to reference the variable a.id from
> within the Regular Expression.
>
> The find function WOULD have worked except, in this case, ids are not
> fixed length. =A0(So there might be some unwanted matches to ids that
> appear within other ids)
>
> Any help is appreciated :)

On Jan 8, 5:21=A0pm, Tom Abernathy <tom.aberna...@gmail.com> wrote:
> What about the FINDW function?
>
> where findw(b.id_list,a.id,',')
>
> On Jan 8, 5:14=A0pm, Praveen Sawh <praveens...@gmail.com> wrote:
>
> > I want to join 2 tables on the condition that variable X from Table 1
> > appears in a comma separated list character variable in Table 2.
>
> > What is the best way to approach this?
>
> > I was trying to use REGEX in something like this:
>
> > PROC SQL;
> > =A0 =A0 =A0 =A0 CREATE TABLE test AS
> > =A0 =A0 =A0 =A0 SELECT =A0*
> > =A0 =A0 =A0 =A0 FROM a LEFT JOIN b
> > =A0 =A0 =A0 =A0 ON prxmatch("/(^a.id)|(a.id$)|(,a.id,)/", STRIP(b.id_li=
st));
> > QUIT;
>
> > But I don't think there is anyway to reference the variable a.id from
> > within the Regular Expression.
>
> > The find function WOULD have worked except, in this case, ids are not
> > fixed length. =A0(So there might be some unwanted matches to ids that
> > appear within other ids)
>
> > Any help is appreciated :)

OMG, AWSOME AWSOME AWSOME. Thanks Tom!