Can I use %INDEX to search a macro list?

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Can I use %INDEX to search a macro list?

[ Edited ]

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.

 

 


Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 8,069

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

Posted in reply to AreYouLikeNew

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;

View solution in original post


All Replies
New Contributor
Posts: 3

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

Posted in reply to AreYouLikeNew

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!

Solution
2 weeks ago
Super User
Super User
Posts: 8,069

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

Posted in reply to AreYouLikeNew

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;
New Contributor
Posts: 3

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

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!

Super User
Super User
Posts: 8,069

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

Posted in reply to AreYouLikeNew

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'
Respected Advisor
Posts: 2,981

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

[ Edited ]
Posted in reply to AreYouLikeNew

@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.

--
Paige Miller
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 116 views
  • 0 likes
  • 3 in conversation