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.
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;
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!
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;
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!
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'
@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.
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.
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.