topic How to use macro in where function using like operators in SAS Programming

How to use macro in where function using like operators

k26_23 — Tue, 21 Oct 2014 13:49:39 GMT

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

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

mohamed_zaki — Tue, 21 Oct 2014 14:07:19 GMT

give example of your data specially the activity col

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

Ksharp — Tue, 21 Oct 2014 14:25:11 GMT

An alternative way is symget()

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

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

RW9 — Tue, 21 Oct 2014 14:35:29 GMT

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;

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

k26_23 — Tue, 21 Oct 2014 14:49:25 GMT

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