Vlookup Search and compare strings and update values

[anuragchoubey]

Hi, Fellow community members !!

I am trying to vlookup of two data set and update strings for example:

I am trying to compare the column  "Old trait id" to the dataset B for matches and update the matches in Dataset B by appending "New trait id" with string "OR".

Dataset A

NEW TRAIT ID	OLD TRAIT ID
16070843T	74160T
16070844T	16981T
16070847T	79556T

Dataset B

segmentRule

(74160T OR 16981T) AND (79556T)

Output 

(74160T OR 16070843T OR 16981T OR 16070844T) AND (79556T OR 16070847T)

[ChrisNZ]

This works:

data  A(index=(OLD_TRAIT_ID));
 input NEW_TRAIT_ID $10. OLD_TRAIT_ID $;
cards;
16070843T	74160T
16070844T	16981T
16070847T	79556T
run;
data B;
  SEGMENT_RULE='(74160T OR 16981T) AND (79556T)';
run;
data WANT;
  set B;
  length NEW_RULE $200;
  NEW_RULE=SEGMENT_RULE;
  do I=1 to countw(SEGMENT_RULE,' ()');
    OLD_TRAIT_ID=scan(SEGMENT_RULE,I,' ()');
    if length(OLD_TRAIT_ID)<6 then continue;
    set A key=OLD_TRAIT_ID;   
    if _IORC_ then do;
      _ERROR_=0;
      continue;
    end;      
    NEW_RULE=tranwrd(NEW_RULE,trim(OLD_TRAIT_ID),catx(' OR ',OLD_TRAIT_ID,NEW_TRAIT_ID));    
  end;
run;
proc print noobs; 
  var NEW_RULE;
run;

NEW_RULE
(74160T OR 16070843T OR 16981T OR 16070844T ) AND (79556T OR 16070847T )