topic Re: Searching a variable for multiple strings in SAS Programming

Searching a variable for multiple strings

chuakp — Mon, 14 Dec 2020 00:51:18 GMT

I have a dataset with variable called drug_name. I'm want to subset to observations in which drug_name contains one of 200 strings corresponding to individual molecules. The brute force method is to use index repeatedly. For example, if there were only 3 strings of interest, I could do something like this:

data want; set have; where

index(drug_name, "ACETAMINOPHEN") > 0 or

index(drug_name, "IBUPROFEN") > 0 or

index(drug_name, "DIPHENHYDRAMINE") > 0;

run;

What is the more efficient way to do this? Thanks.

Re: Searching a variable for multiple strings

Astounding — Mon, 14 Dec 2020 00:58:18 GMT

It depends on what you mean by "contains". Doe sit mean the variable's value is an exact match to one of the 200 strings, or does it mean that the one of the 200 strings appears somewhere within the variable's value (possibly with other characters before or after).

Re: Searching a variable for multiple strings

chuakp — Mon, 14 Dec 2020 01:08:36 GMT

To clarify, it does not have to be an exact match. I just want to subset to observations where one of the 200 strings appears somewhere within the variable's value.

Re: Searching a variable for multiple strings

Astounding — Mon, 14 Dec 2020 04:06:20 GMT

Then I think you are stuck. You could remove all the comparison operators. This would give you a valid comparison for one of the strings:

where index(var, 'ACETAMINOPHEN') or .....;

Also some functions have additional parameters that could ignore upper vs. lower case which might be a help.

Re: Searching a variable for multiple strings

andreas_lds — Mon, 14 Dec 2020 06:42:37 GMT

Do you have the list of strings as dataset? Could you post some observations, so that we have something to play with?

SAS can create such a where-expression for you, the following code is hardly tested:

data DrugList;
   input name $upcase20.;
   datalines;
ACETAMiNOPhEN
IBUProFEN
DIPHENHyDRAMiNE
;


data _null_;
   set DrugList end=jobDone;
   file "drug_filter.sas";
   length Buffer $ 100;
   
   if _n_ = 1 then do;
      put 'where';
   end;
   
   Buffer = cats('index(drug_name, ', quote(trim(name)), ')');
   put '  ' Buffer @;
   
   if jobDone then do;
      put;
      put ';';
   end;
   else do;
      put ' or ';
   end;
run;

data want;
   set have;
   %include "drug_filter.sas";
run;

Re: Searching a variable for multiple strings

s_lassen — Mon, 14 Dec 2020 08:30:22 GMT

You can speed things up and simplify your program by using a format or an informat, e.g.:

proc format lib=work;
  invalue drug (upcase)
    "ACETAMINOPHEN",
    "IBUPROFEN",
    "DIPHENHYDRAMINE" =1
    other=0;
run;
    
    
data have;
infile cards truncover;
input text $200.;
cards;
 erew ACETAmINOPHEN werwerrewwerrwe
sdfælø klgdsklæ klgsdf
rewqv IBUPROFEN 3432 4
sgsfdsgfdsgfdgs
werreerw DIPHENHYDRAMINE 3422323442
;run; 

data want;
  set have;
  do _N_=1 to countw(text) until(found);
    found=input(scan(text,_N_),drug.);
    end;
run;

You may need to put some options on the SCAN function to get the words right.

Re: Searching a variable for multiple strings

chuakp — Mon, 14 Dec 2020 12:34:36 GMT

This code works, thanks very much!

Re: Searching a variable for multiple strings

chuakp — Mon, 14 Dec 2020 12:36:55 GMT

Thanks. I think this would work, but the challenge would be that I would have to create a format with 200 values since I'm looking for 200 strings.

Re: Searching a variable for multiple strings

Ksharp — Mon, 14 Dec 2020 12:39:20 GMT

data want; 
set have; 
if prxmatch('/\b(ACETAMINOPHEN|IBUPROFEN|DIPHENHYDRAMINE)\b/',drug_name );
run;

Re: Searching a variable for multiple strings

andreas_lds — Tue, 15 Dec 2020 06:06:15 GMT

@chuakp wrote:

Thanks. I think this would work, but the challenge would be that I would have to create a format with 200 values since I'm looking for 200 strings.

This is not a problem at all. Proc Format accepts a dataset when using the cntlin-option.