topic Re: read macro variable list in SAS Programming

read macro variable list

Stephenjohn — Fri, 20 Aug 2021 11:37:19 GMT

Hello,

There is a list with space in a macro variable. How can i read this to check if i have the same values in the other list.

eg: %let color = red green yellow blue;

Data col;

set brown;

if col in ('&color') then output;

run;

Re: read macro variable list

ballardw — Fri, 20 Aug 2021 11:49:37 GMT

@Stephenjohn wrote:

Hello,

There is a list with space in a macro variable. How can i read this to check if i have the same values in the other list.

eg: %let color = red green yellow blue;

Data col;

set brown;

if col in ('&color') then output;

run;

Macro variables when representing a value need to be enclosed in double quotes. When you place the macro variable inside single quotes, like you have with '&color' then the macro variable does not resolve.

However, you should write a bit of code that execute properly before using any macro variables. Then show us that code so we can show how to properly use a macro variable.

When the macro variable is enclosed in a set of quotes you likely wont get what you want but need to see what values you are actually using.

Consider this for a quick example:

data test;
   col = 'red';
   if col in ('red green yellow blue') then output;
run;

Do you expect the single value of COL to be output to the data set?

Test the code.

Then consider what goes on.

I strongly suspect that you need to consider just how you are making your macro variable as your current one won't work in the IN comparison.

To have a valid IN comparison looking for any of those words you would need

data test;
   col = 'red';
   if col in ('red' 'green' 'yellow' 'blue') then output;
run;

However consider a different way to search for a string as part of a single string:

data test;
   col = 'red';
   if Indexw('red green yellow blue',col)>0 then output;
run;

Re: read macro variable list

Stephenjohn — Fri, 20 Aug 2021 11:55:12 GMT

that was just an example. i have a huge list of values into a macro variable.. Is there any way i can do this for a IN comparison. or compare a variable values with that of the macro variable values.

Re: read macro variable list

andreas_lds — Fri, 20 Aug 2021 12:47:17 GMT

Step 1: Convert the &color to a dataset, with one value in each observation.

Step 2: Use normal merge or join to create dataset "col".

Step 3: Be happy, that the unnecessary usage of macro-variables could be avoided.

Re: read macro variable list

andreas_lds — Fri, 20 Aug 2021 12:59:46 GMT

But if you insist on using a macro-variable, you have two options:

Fix the step creating the list, so that every value is quoted, so that it can be used in a data step like the one you have posted, just don't quote &color there.
Follow @ballardw suggestion using the function indexw, but replace the list of colors with "&Color".

Using

options symbolgen;

when debugging code with macro-variables is recommended.

Re: read macro variable list

ballardw — Fri, 20 Aug 2021 14:42:58 GMT

@Stephenjohn wrote:

that was just an example. i have a huge list of values into a macro variable.. Is there any way i can do this for a IN comparison. or compare a variable values with that of the macro variable values.

Not sure why the insistence on IN but your macro variable would have to look like a valid list of values for the IN operator, i.e. every value enclosed in quotes. Typically a "huge of list of values" means that a macro variable is the wrong approach from one of several standpoints.

Another possible approach that can test individual values against a list is a custom format.

Proc format;
value $testlist
'abc', 'pdq', 'xyz'="In list"
other='Not in list'
;

data test;
   input col $;
   if put(col,$testlist.)='In list' then output;
datalines;
abc
bbb
bq
pdq
;

Re: read macro variable list

Ksharp — Sat, 21 Aug 2021 10:14:52 GMT

Data col;

set brown;

if findw("&color", strip(col) ) then output;

run;