topic Re: IN a LIST in SAS Enterprise Guide

IN a LIST

jyu068 — Fri, 27 Mar 2020 12:51:42 GMT

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.

Re: IN a LIST

PeterClemmensen — Fri, 27 Mar 2020 12:54:13 GMT

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

Re: IN a LIST

jyu068 — Fri, 27 Mar 2020 12:56:53 GMT

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

Re: IN a LIST

Astounding — Fri, 27 Mar 2020 13:08:45 GMT

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.

Re: IN a LIST

Tom — Fri, 27 Mar 2020 13:17:36 GMT

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.

Re: IN a LIST

PeterClemmensen — Fri, 27 Mar 2020 13:22:50 GMT

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;