Hello all,   I have two datasets.   DATA HAVE1; INPUT (NAME1) (:$8.); CARDS; ERIC Stewart Eri John ERI Abe Eric Mars Eris Eric MARSTIN ; run; DATA HAVE2; INPUT (NAME2) (:$8.); CARDS; Eric Stewart Eri Johnny Eri Lee Eric Swift Eric Eric Strong ; run; What I want is to fuzzy match the NAME1 with NAME2. If NAME2 is similar with NAME1, then keep NAME2. Otherwise, remove NAME2. 1. They don't need to be perfectly same. Mostly match would be fine. 2. Regardless of the case. uppercase and lowercase match can be less important. As long as they are the same letter would be OK. Here is what I want DATA WANT; INPUT (NAME2) (:$8.); CARDS; Eric Stewart Eri Johnny ; run; Would this be achieved through SQL (because I have more other variables to group by and control for) I know some codes such as LIKE CATS. But it seems the code is wrong and keeps processing and never ends. proc sql; select a.*, b.* from have1 a, have2 b where a.name1 LIKE cats('%',b.name2,'%'); quit; Thank you very much. Stay warm. ... View more

Hello,   Seems my last post is complicated. I am trying to simplify my case.   I have dataset like this   DATA HAVE1; INPUT (NAME1 NAME2 X1 X2) (:$8.); CARDS; Z J a a Z J b a Z J c b X C b a X C b c X C c b ; run;     1. I would like to compute the number of OVERLAP between distinct X1 and X2. Group by NAME1, NAME2. 2. Take the result of the 1st step, and divide it by the number of total distinct value in X1 and X2. Group by NAME1, NAME2.   Here is the example of what I want.   DATA WANT; INPUT (NAME1 NAME2 OVERLAP OVERLAPALL) (:$8.); CARDS; Z J 2 2/3 X C 2 2/3 ; /* In first row, OVERLAP is 2 because X1 and X2 only have 'a' 'b' in common between Z and J. There is only 2 overlap. 2/3 means OVERLAP divided by the total # of distinct value of both X1 and X2 between Z and J*/ So it is like this: OVERLAP : count(distinct X1 = distinct X2). OVERLAPALL: count(distinct X1 = distinct X2) / count(distinct X1, X2). All the computation above is based on GROUP BY NAME1, NAME2.   Would you please help me with this? Hopefully I make myself clear.   Thank you all. ... View more