Can you use a list of items in a macrovariable in a whare statement and in operator?

[mcook]

there have been many occasions where i've wanted to be able to do this, but have not been able to figure it out.

Given the following table and code to select the values of column C into a macrovariable "foods" separated by a space ( or really any delimiter).

data TestTable;
	input A $ B C $;
	datalines;
Mitch 10 milk
Ed 14 tuna
Phil 45 bread
Bonnie 17 butter
;
run;

proc sql noprint;
	select C into :foods
		separated by ' '
	from TestTable;
quit;

%PUT foods=&foods.;

Is there a way to use that macrovariable in an in operator, such as the following?

proc sql noprint;
	create table RandomTable1 as
		select * from RandomTable2
			where RandomColumn in ("milk" "tuna" "bread" "butter");
quit;

With the values of the macrovariable as the values of the in operator?

[mcook]

Please ignore my misspelling of Where in the title 🙂

[PeterClemmensen]

I don't recommend it, but yes, you can do something like this

See if you can use this as a template

data TestTable;
	input A $ B C $;
	datalines;
Mitch 10 milk
Ed 14 tuna
Phil 45 bread
Bonnie 17 butter
;
run;

proc sql noprint;
	select quote(compress(C)) into :foods
		separated by ', '
	from TestTable
   where C in ("milk", "tuna");
quit;

%PUT foods=&foods.;

proc sql noprint;
	create table want as
		select * from TestTable
			where C in (&foods.);
quit;

[Tom]

Don't use COMPRESS() on the strings.  If they have any leading or embedded spaces then the resulting quoted strings will not match the values in the data.

[Tom]

If you intend to use the macro variable in that way then include quotes in the value so you can simply use the macro variable to replace the hardcoded list of string literals.

select distinct quote(trim(C)) into :foods separated by ' ' from TestTable;
...
where RandomColumn in (&foods)
...

[Kurt_Bremser]

And if you use a comma as separator, you can use the macro variable with the IN operator and in a WHEN statement in a data step SELECT block.

[Tom]

Note that the IN operator does not need commas.

[Kurt_Bremser]

---

@Tom wrote:

Note that the IN operator does not need commas.

---

But it accepts them, while the WHEN statement requires them. So I always use commas, as I've found that I often convert IF/THEN/ELSE statements with an IN operator to SELECT blocks.

[Tom]

---

@Kurt_Bremser wrote:

---

@Tom wrote:

Note that the IN operator does not need commas.

---

But it accepts them, while the WHEN statement requires them. So I always use commas, as I've found that I often convert IF/THEN/ELSE statements with an IN operator to SELECT blocks.

---

Sure but the commas make it difficult to pass the strings as arguments to macro functions or macro calls.

[Quentin]

You have a delimited list of items in a macro variable.  To use it with in IN operator, you need to add quote marks around each item (and may want to add a comma delimiter, for fun).

I'm a big fan of @RichardDeVen 's utitly macro %seplist: https://www.devenezia.com/downloads/sas/macros/index.php?m=seplist

Given a delimited list of items, it will add quotes (or whatever you want) around each item, change the delimiter, etc.

1
2    %let foods=milk tuna bread butter ;
3
4    %put %seplist(&foods,nest=QQ,dlm=%str(,)) ;
"milk","tuna","bread","butter"

You can use like:

proc sql noprint;
	create table RandomTable1 as
		select * from RandomTable2
			where RandomColumn in (%seplist(&foods,nest=QQ,dlm=%str(,)));
quit;

Generally I like to keep my macro variable lists without quote marks, and either space-delimited or pipe-delimited.  %SepList makes it easy to change delimiters or add quote marks when needed.

[mcook]

Soon after posting I had an idea. And wrote a quick macro to add quotes around each of the values in the macrovariable.  

So far it seems to work correctly. With the constraint that the values be space delimited, with no internal spaces.

*InVars must be space separated with no internal spaces in the values;
*VarName is Name of Global Variable ouputted, with quotes around each value.;
%MACRO K(VarName,InVars);
	%LET MVar1=;

	%DO J=1 %TO %SYSFUNC(CountW(&InVars.));
		%LET JJ=%SCAN(&InVars.,&J.);
		%LET quote&J.="&JJ.";
		%LET MVar1=&MVar1. &&quote&J.;
	%END;

	%GLOBAL &VarName.;
	%LET &VarName.=&MVar1.;
	%PUT &Varname.=&MVar1.;
%MEND K;

[Tom]

Looks good.  Make sure to define your local macros using %LOCAL statement so that programs that call that macro don't get supprised when the value their macro variables named MVAR1 and JJ and the others you are creating get changed.

Also look into making it a "function" macro.  Like the one mentioned above. Or this one:  https://github.com/sasutils/macros/blob/master/qlist.sas

[mcook]

Arent macrovariables created within a macro local to that macro by default?  With any other macrovariables outside that macro unaffected, regardless of having the same name?  

[Tom]

Macro variables created within a macro are only local to that macro if the macro variable does not already exist.

If the macro variable with that name already exists (either GLOBAL or in the local scope of another macro that has called this one) then that macro variable is used.

The purpose of the %LOCAL statement is to insure that your macro is not changing macro variables previously defined.  So to avoid unwanted side effects of running the macro.

[ballardw]

Try this code:

%macro prob(x=);
%do i = 1 %to &x;
  %put Writing i in Prob: &i;
%end;
%mend;
%macro driver(n=);
%do i= 1 %to &n;
   %prob(x=3)

   %put Writing i in Driver: &i.;
%end;
%mend;

%driver(n=2)

Then add a %local i to the Prob macro and recompile the macro and run the code.

See the difference?