Delete all observations starting with a list of values from database (SAS)

I am trying to find the optimized way to do this :

I want to delete from a character variable all the observations STARTING with different possible strings such as :

"Subtotal" "Including:" So if it starts with any of these values (or many others that i didn't write here) then delete them from the dataset.

Best solution would be a macro variable containing all the values but i don't know how to deal with it. (%let list = Subtotal Including: but counts them as variables while they are values)

I did this :

data a ; set b ; if findw(product,"Subtotal") then delete ; if findw(product,"Including:") then delete; ... ...

Would appreciate any suggestions !Thanks

Solution

First figure out what SAS code you want. Then you can begin to worry about how to use macro logic or macro variables.

Do you just to exclude the strings that start with the values?

data want ;
  set have ;
  where product not in: ("Subtotal" "Including");
run;

Or do you want to subset based on the first "word" in the string variable?

  where scan(product,1) not in ("Subtotal" "Including");

Or perhaps case insensitive?

  where lowcase(scan(product,1)) not in ("subtotal" "including");

Now if the list of values is small enough (less than 64K bytes) then you could put the list into a macro variable.

  %let list="Subtotal" "Including";

And then later use the macro variable to generate the WHERE statement.

  where product not in: (&list);

You could even generate the macro variable from a dataset of prefix values.

proc sql noprint;
  select quote(trim(prefix)) into :list separated by ' '
    from prefixes 
  ;
quit;