topic Re: How to create a macro variable containing an array/list of values? in New SAS User

How to create a macro variable containing an array/list of values?

osbornejo — Fri, 14 Jan 2022 14:49:06 GMT

I want to create a macro variable that contains multiple values to check against. Example pseudocode:

%LET myCodes = [Not sure how to do this part];

/*Ideally &myCodes would return a list/array of the codes I wanted. i.e. Code1, Code2,...*/

...

proc sql;

create table myTable as

select * from DataTable /*Note: Assume DataTable has a column named Code*/

where Code in &myCodes;

quit;

/pseudocode

What I am having trouble with is defining myCodes. I had the thought of just defining it as

%LET myCodes = Code1 Code2 ...;

but that would just make myCode be equivalent to a single long string, correct? I'd imagine that has the potential to cause problems. Even if I separated them (e.g. 'Code1','Code2',...) my understanding is that I would just end up with a string that contains all the punctuation.

Am I misunderstanding something or is there a different technique to do this?

Edit: The Codes are alphanumeric (e.g. A1234). The values in myCodes will be entered by hand. I'm trying to filter a dataset for observations with alphanumeric CODE that matches one of the codes in myCodes.

Re: How to create a macro variable containing an array/list of values?

Tom — Thu, 13 Jan 2022 23:33:53 GMT

If CODE is a numeric variable then just use a space delimited list:

%let mycodes=1 3 5 ;
....
  where code in (&mycodes)
....

If CODE is a character variable then include quotes around the values:

%let mycodes='A' "B" 'C' ;
....
  where code in (&mycodes)
....

Are you planning to type the list of codes by hand? Or generate it from a dataset?

If you have the list in a dataset then use PROC SQL to generate the list into a macro variable.

For character values use the QUOTE() function to add the quotes. No need to include the trailing spaces that SAS stores in its fixed length character variables.

proc sql noprint;
select code into :mycodes separated by ' ' from mylist;
select quote(trim(code)) into :mycodes separated by ' ' from mylist;
quit;

Re: How to create a macro variable containing an array/list of values?

SASKiwi — Fri, 14 Jan 2022 00:07:12 GMT

What are you intending to use the list for? There could be better ways to solve your use case if you explain what it is.

Re: How to create a macro variable containing an array/list of values?

Quentin — Fri, 14 Jan 2022 03:25:20 GMT

Hi,

There are lots of ways to create a macro variable containing a list of values. The easiest is to just type the list.

I think your question is partly about how macro variables work. The key thing to keep in mind is that macro variables are for text substitution. You can assign a text value to a macro variable, and when the macro variable resolves it will substitute that text into the code.

Here's a valid SQL step with no macro variables

proc sql ;
  create table class as
  select * from sashelp.class
  where Name IN ("Mary" "John") 
;
quit ;

If you want to store the list of students' names in a macro variable, you could do it like:

%let myNames="Mary" "John" ;

proc sql ;
  create table class as
  select * from sashelp.class
  where Name IN (&myNames) 
;
quit ;

When the PROC SQL step is compiling (or I guess being interpreted), the macro reference &myNames will resolve to "Mary" "John". So both PROC SQL steps are exactly the same.

When you're starting to work with macro variables, the key is to remember that all they are doing is text substitution. And when you start writing macros, the key is to remember that all they are doing is text generation.

Hope that helps,

--Q.

Re: How to create a macro variable containing an array/list of values?

Kurt_Bremser — Fri, 14 Jan 2022 08:18:08 GMT

Rule #1 of macro development: start with working non-macro SAS code.

So you first need to write down the working SQL code for your condition. Once you have that, it will be obvious what has to go into the macro variable. Keep in mind that the macro preprocessor is just a text replacement tool.

Re: How to create a macro variable containing an array/list of values?

osbornejo — Fri, 14 Jan 2022 14:50:59 GMT

@SASKiwi I'm trying to use it to filter a dataset for observations with alphanumeric CODE that matches one of the codes in myCodes.

Re: How to create a macro variable containing an array/list of values?

osbornejo — Fri, 14 Jan 2022 14:50:13 GMT

@Tom CODE is alphanumeric, and I'll be entering myCodes by hand.

Re: How to create a macro variable containing an array/list of values?

osbornejo — Fri, 14 Jan 2022 14:54:16 GMT

@Quentin That's a helpful explanation, thank you. I guess I'm too used to Python, R, Java, and the like, where the type of an object is more important.

Re: How to create a macro variable containing an array/list of values?

Tom — Fri, 14 Jan 2022 16:20:29 GMT

@osbornejo wrote:

@Quentin That's a helpful explanation, thank you. I guess I'm too used to Python, R, Java, and the like, where the type of an object is more important.

The type of a variable in SAS code does make a difference. That is why you need to place quotes around the values so the SAS interpreter will see them as string literals instead of variable names.

But the macro processor is not a programming language. It is just as its name suggests a processor for expanding the text before it is passed onto the real language to be compiled/interpreted.

Re: How to create a macro variable containing an array/list of values?

Quentin — Fri, 14 Jan 2022 16:53:18 GMT

Exactly, the macro language does not have objects. And it does not know anything about SAS datasets or variables that exist in SAS datasets.

While we talk about "macro variable lists" and even "macro arrays" the values are always just text strings, and sometimes those text strings are useful to think of as a list or array. But that is all in the macro programmer's head. The macro language thinks everything is just text strings.