DATA Step, Macro, Functions and more

How to use macro in where function using like operators

Reply
Occasional Contributor
Posts: 5

How to use macro in where function using like operators

I want to use the where function with like operators for data sub setting but the activity can contain anything like poultry processing, poultry slaughter, Meat processing, meat slaughter and so on, now i have to resolve the macro to poultry masking only the % sign?  Example of the code is given below, which function is best in this situation?

%let activity= Poultry;

PROC SQL outobs= 500 NOPRINT;

  create table work.testdata as

     select var1, var2, activities

  from retailstore

  where activities like "%&activity%"

;

QUIT;

the activity variable contains the following value

Poultry processing, Meat Processing, Poultry slaughter

Poultry slaughter, Meat Processing

Ready to eat Poultry, Poultry slaughter

Meat Processing, Import

Meat slaughter, Import, Meat Processing

Ready to eat meat, Meat Processing

Import

Super Contributor
Posts: 490

Re: How to use macro in where function using like operators

Posted in reply to karthigao

give example of your data specially the activity col

Occasional Contributor
Posts: 5

Re: How to use macro in where function using like operators

Posted in reply to mohamed_zaki

activity

Poultry processing, Meat Processing, Poultry slaughter

Poultry slaughter, Meat Processing

Ready to eat Poultry, Poultry slaughter

Meat Processing, Import

Meat slaughter, Import, Meat Processing

Ready to eat meat, Meat Processing

Import

This may occur in any combination, out of which i want to extract either poultry, meat, export, import to find out how many poultry centers (processing, slaughter, ready to eat), how many meat centers, and import, expor

Super User
Posts: 10,041

Re: How to use macro in where function using like operators

Posted in reply to karthigao

An alternative way is symget()

where  find(activities,symget('activity') )

Super User
Super User
Posts: 7,977

Re: How to use macro in where function using like operators

Agree with KSharp there.  A further alternative is to put your items in a table and subquery that.  E.g:

data activities;

     activity="Poultry Processing"; output;

      activity="Meat processing"; output;

...

run;

proc sql;

     select   *

     from     HAVE

     where   ACTIVITY in (select distinct ACTIVITY from ACTIVITIES);

quit;

Ask a Question
Discussion stats
  • 4 replies
  • 217 views
  • 3 likes
  • 4 in conversation