Compare two variables and output the matching string

[augustcrez]

Hi,

I have two columns: 1. is my list of treatment drugs given to a patient (multiple, can have the study drug or cannot have the study drug) and 2 is my list of drug related to the study(separated by a coma, constant/fixed).

My requirement is i have to search for rows where my list of study drugs has been given to the patient. and display all those separated by coma.

I have my main dataset to which each row i have merged my list of drug(combined into coma separated).

VAR1                                                      VAR2                     WANT

1.LMK 2.ABC NM 3.ABC 4.XYZ           ABC, XYZ               ABC, XYZ

1.ABC                                                   ABC, XYZ               ABC

1.XYZ                                                   ABC, XYZ               XYZ

Please help!

Thanks.

[PeterClemmensen · Accepted solution]

Do something like this

data have;
length VAR1 $50;
input VAR1 $ VAR2 $;
infile datalines dlm='|';
datalines;
1.LMK 2.ABC NM 3.ABC 4.XYZ|ABC, XYZ
1.ABC|ABC, XYZ
1.XYZ|ABC, XYZ
;

data want(drop=i string);
   set have;
   length want $100;
   do i=1 to countw(VAR2);
      string=strip(scan(VAR2, i, ','));
      if find(VAR1, string, 'it') > 0 then want=catx(',', want, string);
   end;
run;

[augustcrez]

Hi ,

Both the solutions work fine for me in most of the rows but i also found a case which is as follows 

data have;
length VAR1 $50 var2 $50;
input VAR1 $ VAR2 $;
infile datalines dlm='|';
datalines;
1.LMK 2.ABCNM 3.XYZ|ABCNM, XYZ, NM
1.ABC|ABCNM, XYZ, NM
1.XYZ|ABCNM, XYZ, NM
;
run;

here in my first now my resultant should be only "ABCNM, XYZ" but i get "ABCNM,XYZ,NM" which shouldnt be the case as im looking for exact matches

Also can i apply formats to these drugnames at the same time?

Please help!

Thanks

[Jagadishkatam]

Please try the below code

    
data want;
set have;
count=countw(var2,',');
array xvar(*) $ xvars1-xvars10;
do i = 1 to count;
if index(var1,strip(scan(var2,i,','))) then xvar(i)=scan(var2,i,',');
end;
newvar=catx(',',of xvars1-xvars10);
drop xvars:;
run;

[augustcrez]

Hi ,

Both the solutions work fine for me in most of the rows but i also found a case which is as follows 

data have;
length VAR1 $50 var2 $50;
input VAR1 $ VAR2 $;
infile datalines dlm='|';
datalines;
1.LMK 2.ABCNM 3.XYZ|ABCNM, XYZ, NM
1.ABC|ABCNM, XYZ, NM
1.XYZ|ABCNM, XYZ, NM
;
run;

here in my first now my resultant should be only "ABCNM, XYZ" but i get "ABCNM,XYZ,NM" which shouldnt be the case as im looking for exact matches

Also can i apply formats to these drugnames at the same time?

Please help!

Thanks