Looking up strings in one column and matching in another.

[hwangnyc]

Hi everyone,

I have string data in two columns that are separated by commas. I'd like to scan the first column's characters and see if it appears in the second column.

Here is some sample data:

HaveA	HaveB
Homeless, Health Care, Aided	Health Care, Verified, Aided
A,B,C	A,C
X,Y,Z	J,K,F

This is what I want:

HaveA	HaveB	Want
Homeless, Health Care, Aided	Health Care, Verified, Aided	Health Care, Aided
A,B,C	A,C	A,C
X,Y,Z	J,K,F	.

Many thanks in advance!

[ballardw]

Untested without data but has a chance of to get started.

data want;
   set have;
   length wantstr $100; /* or long enough to hold expected longest value*/
   do i= 1 to countw(HaveA,',');
      if index(haveb, scan(havea,i,','))>0 then wantstr=catx(',',wantstr,scan(havea,i,','));
   end;
   drop i;
run;

Countw  and Scan functions are told to only consider comma as delimiter for finding phrases,