topic Re: Can you use a list of items in a macrovariable in a where statement and in operator? in SAS Programming

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

mcook — Fri, 08 Jan 2021 13:45:38 GMT

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?

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

mcook — Fri, 08 Jan 2021 13:47:35 GMT

Please ignore my misspelling of Where in the title 🙂

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

PeterClemmensen — Fri, 08 Jan 2021 13:50:21 GMT

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;

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

Tom — Fri, 08 Jan 2021 13:51:01 GMT

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

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

Tom — Fri, 08 Jan 2021 13:54:39 GMT

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.

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

Kurt_Bremser — Fri, 08 Jan 2021 14:03:26 GMT

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.

Re: Can you use a list of items in a macrovariable in a where statement and in operator?

Tom — Fri, 08 Jan 2021 14:19:58 GMT

Note that the IN operator does not need commas.

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

Quentin — Fri, 08 Jan 2021 14:50:34 GMT

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.

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

mcook — Fri, 08 Jan 2021 15:31:51 GMT

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;

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

Tom — Fri, 08 Jan 2021 15:59:11 GMT

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

Re: Can you use a list of items in a macrovariable in a where statement and in operator?

Kurt_Bremser — Fri, 08 Jan 2021 16:51:43 GMT

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

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

mcook — Fri, 08 Jan 2021 18:01:40 GMT

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?

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

Tom — Fri, 08 Jan 2021 18:13:25 GMT

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.

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

ballardw — Fri, 08 Jan 2021 18:14:58 GMT

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?

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

Ksharp — Sat, 09 Jan 2021 13:16:00 GMT



%let food= SUV Sedan Sports ;

proc sql;
create table want as
 select * from sashelp.cars
  where findw(symget('food'),strip(type));
quit;

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

mcook — Mon, 11 Jan 2021 15:07:04 GMT

Ah that works nicely. Thank you

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

mcook — Wed, 13 Jan 2021 14:51:40 GMT

Was shown this way to accomplish this as well.

Given the TestTable from my original post.

proc sql noprint;
	select '"'||strip(C)||'"' into :foods
		separated by ' '
	from TestTable;
quit;

%PUT Foods=&Foods.;

This also works with the distinct predicate. such as:

proc sql noprint;
	select distinct '"'||Strip(type)||'"' into :CarTypes
		separated by ' '
	from sashelp.cars;
quit;

%PUT CarTypes=&CarTypes.;

Re: Can you use a list of items in a macrovariable in a where statement and in operator?

Tom — Wed, 13 Jan 2021 14:58:17 GMT

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

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

Tom — Wed, 13 Jan 2021 15:01:29 GMT

Two problems with

'"'||strip(C)||'"'

First is that using STRIP() instead of TRM() will remove any leading spaces from the values of C. So that the quoted strings will now no longer match the value that is in the dataset.

Second is when the value of C contains quote characters. You need to double any quote characters to make a valid string literal.

Better to use the TRIM() and QUOTE() functions.

quote(trim(C))