SAS Hash Macro to exclude records with certain words

[SASsy05]

Hi,

I am trying to write a hash macro in SAS 9.4 that will text match medication names in a dataset to a list of terms in another dataset. I want it to output if there is a text match but only if it doesn't include certain terms. This is what I have (taken out of the macro to see if I could get it to work). It works if I don't include the exclusion term (s).

  data step2;
    length drug_name $ 100;
    length x_drug_name $ 50;
    declare Hash Drug ();
    rc = Drug.DefineKey ('drug_name');
    rc = Drug.DefineData ("drug_cid");
    rc = Drug.DefineDone ();

    do until (eof1) ;
      set Med_Aspirin end = eof1;
      rc = Drug.add ();
    end;

    call missing(drug_name); /* initialize the variable we intend to fill */
    call missing(drug_cid); /* initialize the variable we intend to fill */

    do until (eof2) ;
    set sample end = eof2;
      where  drug_concept_id=0;
      do i = 1 to 5;  *cycle thru the text elements (up to 5) of x_drug_name looking for a match with a name on the name list;
        drug_name=scan(upcase(x_drug_name),i,' -/!*#\,');
        rc = Drug.find ();
        if rc=0 then output;  * this is where I was hoping to say if rc=0 and upcase(x_drug_name) ne caffeine anywhere in the field, then output;

      
      end;

    end;

   keep drug_exposure_id
           drug_exposure_start_date
           x_drug_name
           drug_name
           drug_concept_id
           drug_cid;

   stop;
  run;

Thank you for any help and suggestions you may have!

[PeterKellberg]

Hi,

Haven't got into details but shouldn't it be

...and upcase(x_drug_name) ne "CAFFEINE"

[SASsy05]

You would think! But unfortunately it doesn't probably because x_drug_name may look something like this: acetaminophen/aspirin/caffeine 250 mg-520 mg-32.5

[PeterKellberg]

ah ok well this could do it:

...and find(x_drug_name,"caffeine","i")

The "i" modifier for the FIND functions will make the search case insensitive

[SuryaKiran]

Exactly What I mean to say.

[PeterClemmensen]

Could you provide some sample data?

[SASsy05]

x_drug_name

acetaminophen/aspirin/caffeine 250 mg-520 mg-32.5

aspirin-acetaminophen-caffeine (EXCEDRIN MIGRAINE)

aspirin 81 mg oral enteric coated capsule

At some point I am going to need to exclude a record if it contains many different terms, not just caffeine.

[SuryaKiran]

Try using FIND() function instead of SCAN()

[ballardw]

You may well want to consider instead of

 do i = 1 to 5; 

which may have issues with single words and will fail if the word you look for is in a string with more than 5 words to use

do i = 1 to Countw(text);

If using Scan then you also want to make sure that Countw and Scan use the same definition of word delimiter.