topic Re: SAS Hash Macro to exclude records with certain words in SAS Programming

SAS Hash Macro to exclude records with certain words

SASsy05 — Mon, 31 Oct 2016 18:44:59 GMT

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!

Re: SAS Hash Macro to exclude records with certain words

PeterKellberg — Tue, 01 Nov 2016 10:14:02 GMT

Hi,

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

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

Re: SAS Hash Macro to exclude records with certain words

PeterClemmensen — Tue, 01 Nov 2016 12:26:18 GMT

Could you provide some sample data?

Re: SAS Hash Macro to exclude records with certain words

SASsy05 — Tue, 01 Nov 2016 13:16:45 GMT

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

Re: SAS Hash Macro to exclude records with certain words

SASsy05 — Tue, 01 Nov 2016 13:18:16 GMT

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.

Re: SAS Hash Macro to exclude records with certain words

SuryaKiran — Tue, 01 Nov 2016 14:15:15 GMT

Try using FIND() function instead of SCAN()

Re: SAS Hash Macro to exclude records with certain words

PeterKellberg — Tue, 01 Nov 2016 14:34:04 GMT

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

Re: SAS Hash Macro to exclude records with certain words

ballardw — Tue, 01 Nov 2016 14:36:38 GMT

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.

Re: SAS Hash Macro to exclude records with certain words

SuryaKiran — Tue, 01 Nov 2016 14:40:03 GMT

Exactly What I mean to say.