IN a LIST

[jyu068]

Now I have 2 tables,

Data table1

column1	column2
A	E1
A	AE2
A	5

and reference table 2

column1	column2
A	E1,AE2,3

what I want to implement is to create a matching flag calculation like the code below:

case
when t1.column1 = t2.column1 and t1.column2 in t2.column2
then 1
else 0
end

but it has a syntax error. any suggestions?

Thanks.

[PeterClemmensen]

So you want to flag the two first records, correct?

[jyu068]

hi I want to flag the rows in t1 which column1 has the same value as t2.column1 and t1 column2's value is one of the t2 column2 value.
Thanks

[PeterClemmensen · Accepted solution]

Try this

data a;
input column1 $ column2 $;
datalines;
A E1
A AE2
A 5
;

data b;
input column1 $ column2 $;
datalines;
A E1,AE2,3
;

data c;
   merge a b (rename=column2=column3);
   by column1;
   dummy = indexw(column3, trim(column2), ',') > 0;
run;

[Astounding]

Your table 2 isn't a reference table.  It's a nightmare.

The programming cam be done, but you need to do it each time you want to use the reference table.  Instead of that, convert table2 so that it has the same format as table 1.  That will simplify the matching process and make it very flexible.  Merging, hashing, formats all become possible techniques with a better structure for the reference table.

[Tom]

IN operator is for test a series of values, not search in a string.

Use the INDEXW() function (or FINDW() function) to check if a word exists in a delimited string.