topic Re: Compare two variables and output the matching string in SAS Programming

Compare two variables and output the matching string

augustcrez — Tue, 06 Nov 2018 07:16:58 GMT

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.

Re: Compare two variables and output the matching string

PeterClemmensen — Tue, 06 Nov 2018 08:31:35 GMT

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;

Re: Compare two variables and output the matching string

Jagadishkatam — Tue, 06 Nov 2018 09:36:58 GMT

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;

Re: Compare two variables and output the matching string

augustcrez — Mon, 12 Nov 2018 04:49:19 GMT

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

Re: Compare two variables and output the matching string

augustcrez — Mon, 12 Nov 2018 09:16:42 GMT

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