DATA Step, Macro, Functions and more

SAS Hash Macro to exclude records with certain words

Reply
Occasional Contributor
Posts: 13

SAS Hash Macro to exclude records with certain words

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!

Occasional Contributor
Posts: 8

Re: SAS Hash Macro to exclude records with certain words

Hi,

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

...and upcase(x_drug_name) ne "CAFFEINE"
Occasional Contributor
Posts: 13

Re: SAS Hash Macro to exclude records with certain words

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

Occasional Contributor
Posts: 8

Re: SAS Hash Macro to exclude records with certain words

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

 

Frequent Contributor
Posts: 117

Re: SAS Hash Macro to exclude records with certain words

Exactly What I mean to say.

PROC Star
Posts: 547

Re: SAS Hash Macro to exclude records with certain words

Could you provide some sample data?

Occasional Contributor
Posts: 13

Re: SAS Hash Macro to exclude records with certain words

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.

 

Frequent Contributor
Posts: 117

Re: SAS Hash Macro to exclude records with certain words

Try using FIND() function instead of SCAN()

Super User
Posts: 10,466

Re: SAS Hash Macro to exclude records with certain words

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.

Ask a Question
Discussion stats
  • 8 replies
  • 353 views
  • 0 likes
  • 5 in conversation