topic Re: Can I use %INDEX to search a macro list? in SAS Programming

Can I use %INDEX to search a macro list?

AreYouLikeNew — Mon, 02 Jul 2018 16:27:28 GMT

Hi all,

I've created a list &WestAfrlist in proc sql (not shown). The %put looks great and produces a list of countries in W. Africa. Now, I want to search that list for a match in the variable "country1" for each observation in my dataset. In other words, if for an observation country1=Benin, I'd like to search WestAfrlist for Benin and return a value of 1 for "region_ind". With the bad code below, region_ind doesn't initialize and ERROR: Variable REGION_IND not found.

Version 9.4.

%macro abc(dv);

%let findabc=%index(&WestAfrlist.,dv);

%if &findabc=1 %then %do;

region_ind=1;

%end;

%mend abc;

data travel2017;

set travelhx;

if country1='NA' then country1="";

%abc (country1);

run;

Any ideas on how to accomplish this? Thanks in advance.

Re: Can I use %INDEX to search a macro list?

AreYouLikeNew — Mon, 02 Jul 2018 16:42:44 GMT

Ok, I just realized that my "list" is simply concatenated strings. Am working on getting it through the first observation, unless someone has a better idea first!

Re: Can I use %INDEX to search a macro list?

Tom — Mon, 02 Jul 2018 16:55:33 GMT

You could create the list in a way that makes is useful for generating an IN () condition test in SAS code.

proc sql noprint ;
  select quote(trim(country)) into :list separated by ' '
  from source_table
  where region='West Africa'
  ;
quit;

Then just expand the list where you would normally have to type string literals.

if country1 in (&list) then region_ind=1 ;
else region_ind=0;

Re: Can I use %INDEX to search a macro list?

PaigeMiller — Mon, 02 Jul 2018 17:14:13 GMT

@AreYouLikeNew wrote:

data travel2017;

set travelhx;

if country1='NA' then country1="";

%abc (country1);

run;

You can't use a data step variable as an argument in the call to %abc

But, if you have the macro list of countries in West Africa, you don't need further macros to do this. You can use data step code, and the data step INDEX or FIND function to achieve what you want.

UNTESTED CODE

data want;
    set have;
    if find("&westafrlist",country1,'it')>0 then region_ind=1;
    else region_ind=0;
run;

As a side comment, using readable variable names and readable macro variable names (as shown in my example)would be very helpful to you when you program, and to us in this forum when we are trying to help, rather than &dv and %abc and &findabc.

Re: Can I use %INDEX to search a macro list?

AreYouLikeNew — Mon, 02 Jul 2018 17:14:43 GMT

Very odd. I tried this method using in my proc sql like so:

select compress(WestAfr,"'") into :WestAfrlist separated by ' '

from work.data

and that didn't work (I had Cote d'Ivoire, thus the compress)!

However, your quote(trim( worked perfectly. I'll have to check the SAS docs to get the specifics on quote trim. Thank you Tom and thank you Paige!

Re: Can I use %INDEX to search a macro list?

Tom — Mon, 02 Jul 2018 17:45:19 GMT

In SAS code you need to put string literals in quotes so that SAS doesn't think you are referencing a variable.

The QUOTE() function will add quotes around a value. So

Cote d'Ivoire

becomes

"Cote d'Ivoire"

or if you used the optional second argument to tell QUOTE() to use single quotes then it would become

'Cote d''Ivoire'