BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AreYouLikeNew
Calcite | Level 5

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

5 REPLIES 5
AreYouLikeNew
Calcite | Level 5

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!

Tom
Super User Tom
Super User

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;
AreYouLikeNew
Calcite | Level 5

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!

Tom
Super User Tom
Super User

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'
PaigeMiller
Diamond | Level 26

@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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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