multiple Macro values issue - better way to apply filter on these values on a dataset

[Kirtid]

Hi,

I have a macro variable which has multiple values in it separated by a ',' . I want to use these macro variable values to filter records from a dataset, can u please suggest a better way to do it, I understand we can use array loop to do this but is there any other ways in which it can be implemented?

Sample code:

Data abc;

input month$ sales;

cards;

jan 20

feb 50

feb 60

mar 60

;

run;

Macro variable 'mon' has multiple values separated by a ','

%let mon = jan, feb;

Now I want to filter records in dataset abc by applying these macro values, the below case might error and I'm looking for better way to implement this step, so my final output should have only the records that matches for jan and feb months

data b;

set abc;

where month in &mon.; 

run;

expected output :

month sales

jan 20

feb 50

feb 60

[Reeza]

How is that macro variable created? As shown it's problematic because no quotes, but depending on how you're creating it there are ways to work around that or add it in. 

---

@Kirtid wrote:

Hi,

I have a macro variable which has multiple values in it separated by a ',' . I want to use these macro variable values to filter records from a dataset, can u please suggest a better way to do it, I understand we can use array loop to do this but is there any other ways in which it can be implemented?

Sample code:

Data abc;

input month$ sales;

cards;

jan 20

feb 50

feb 60

mar 60

;

run;

Macro variable 'mon' has multiple values separated by a ','

%let mon = jan, feb;

Now I want to filter records in dataset abc by applying these macro values, the below case might error and I'm looking for better way to implement this step, so my final output should have only the records that matches for jan and feb months

data b;

set abc;

where month in &mon.; 

run;

expected output :

month sales

jan 20

feb 50

feb 60

---

[Kirtid]

Thanks for your reply Reeza. The macro values are created from another dataset from where we get the unique month information. Would it work by having single quotes around the values? - something like this : %let mon = 'jan','feb';

[Reeza]

---

@Kirtid wrote:

Thanks for your reply Reeza. The macro values are created from another dataset from where we get the unique month information. Would it work by having single quotes around the values? - something like this : %let mon = 'jan','feb';

---

Yes, change your macro variables. 

You can create your list from SQL using the following which creates a quoted list.

proc sql noprint;
select quote(name) into :name_list separated by " "
from sashelp.class;
quit;

%put &name_list.;

Or you can just use the data directly from the data set, assuming it's called sublist, as:

proc sql;
create table subset as
select *
from sashelp.class
where name in (select name from sublist);
quit;

[ballardw]

Why did you place the comma in the first place?

And the syntax for using IN requires the values to be within ( ) and character values have to be in quotes

Example:

data want;
   set sashelp.class;
   where name in ('John' 'Alice' 'James' 'Philip');
run;

When the IN operator was first introduced to SAS it did require a comma delimited list but that has been gone for quite awhile.

As long as your case of the values in you data set matches I would expect this to work.

%let mon = 'jan'  'feb';
Data abc;
input month$ sales;
cards;
jan 20
feb 50
feb 60
mar 60
;
run;
data b;
   set abc;
   where month in ( &mon.); 
run;

If you case is not consistent such as values like Jan and jan both appearing you'll need to address that. Hint: function UPCASE may help.

[Kirtid]

Thanks for your reply.. hmm, but the problem is that the macro values are separated by a comma as these values are to be be used for different other purposes, so I may not be able to adjust or edit the way they appear and I just brought this month only as a sample case.

if my macro values are still appearing with comma separation, how can we still make it work ?

[Tom]

Your original problem was just the lack of () around the list.  If the values are space delimited then combining two lists is easy.

where x in (&list1 &list2)

If the values have commas then combing two lists is a pain.

%let list1=a,b;
%let list2=;
%let full_list=&list1;
%if %length(&list2) %then %let full_list=&full_list,&list2;

I find it is usually better to add in the commas when they are needed.  SQL syntax is where it is normally needed.

%let varlist=id date ;
....
by &varlist;
....
%let varlistc=%sysfunc(tranwrd(%sysfunc(compbl(&varlist)),%str( ),%str(,)));
....
order by &varlistc
....

[Kurt_Bremser]

---

@Kirtid wrote:

Thanks for your reply.. hmm, but the problem is that the macro values are separated by a comma as these values are to be be used for different other purposes, so I may not be able to adjust or edit the way they appear and I just brought this month only as a sample case.

if my macro values are still appearing with comma separation, how can we still make it work ?

---

Use an intermediate data step to convert your macro variable to a text you can use in an in-list:

Data abc;
input month$ sales;
cards;
jan 20
feb 50
feb 60
mar 60
;
run;

%let mon = jan, feb;

data _null_;
length target $1000;
source = "&mon.";
do i = 1 to countw(source,',');
  target = catx(',',target,quote(strip(scan(source,i,','))));
end;
call symputx('new_mon',target);
run;

data b;
set abc;
where month in (&new_mon.); 
run;

proc print data=b noobs;
run;

Result:

month    sales

 jan       20 
 feb       50 
 feb       60 

[ballardw]

---

@Kirtid wrote:

Thanks for your reply.. hmm, but the problem is that the macro values are separated by a comma as these values are to be be used for different other purposes, so I may not be able to adjust or edit the way they appear and I just brought this month only as a sample case.

if my macro values are still appearing with comma separation, how can we still make it work ?

---

Since passing values with commas is a headache requiring macro quoting and such I use a utility macro to add commas to list of things when needed. I don't remember where I found this:

%macro addcommas(somevars,addmore);
%if %sysevalf(%superq(addmore)=,boolean) %then %let commavalue=%sysfunc(translate(&somevars, %str(,), %str( ) )) ; 
%else %let commavalue=%sysfunc(translate(&somevars &addmore, %str(,), %str( ) ));
&commavalue

%mend; 

This can either be used to assign values to a variable such as:

%let varlist= jan feb mar stupid;
%let other = pdq;
%let varlist2 = %addcommas(&varlist.,&other.)    ;

%put &varlist2.;

or directly in the code that needs the list

%let varlist= name sex age;

proc sql;
   select %addcommas(&varlist.)
   from sashelp.class
   ;
quit;

[Tom]

Looks overly complicated, it shouldn't matter if the second argument has a value or not.

You could make it a little more robust.

%macro addcommas2(somevars,addmore);
%if %length(&somevars.&addmore) %then
%sysfunc(tranwrd(%qsysfunc(compbl(&somevars &addmore)),%str( ),%str(,)));
%mend; 

110   %put %addcommas(x y,z);
x,y,z
111   %put %addcommas2(x y,z);
x,y,z
112   %put %addcommas(x y,);
x,y
113   %put %addcommas2(x y,);
x,y
114   %put %addcommas();
WARNING: Argument 1 to function TRANSLATE referenced by the %SYSFUNC or %QSYSFUNC macro function is out of
         range.

115   %put %addcommas2();

116   %put %addcommas(x   y,z);
x,,,y,z
117   %put %addcommas2(x   y,z);
x,y,z

[Astounding]

You can use your macro variable as is:

data want;
   set have;
   where findw("&mon.", strip(month)) > 0;
run;

Without testing, I'm not sure whether the STRIP function is actually needed here.

[Kirtid]

Thanks all for the giving me different views on this topic. Another approach I used tranwrd func
with cat . Month in strip(Cat ("'",tranwrd(&mon.,",","','"),"'"))

[Tom]

---

@Kirtid wrote:
Thanks all for the giving me different views on this topic. Another approach I used tranwrd func
with cat . Month in strip(Cat ("'",tranwrd(&mon.,",","','"),"'"))

---

That does not look like valid code if the MON macro variable has multiple values, like JAN,FEB.

I am not even sure you can use syntax like that.  Your expression is in the form:

var in expression

I am pretty sure that you can only use 

var in (<list of values>)

If you want IN to check a list of variables then make an array.

array x var1 var2 ....;
...
if var in x;