Macro using multi where statement

[altijani]

Hello Community

I would like to run the following concept using SAS SQL, and I appreciate a confirmation whether this code is correct:

I need to obtain all the data from a group of 4 states only. The code I am planning to use is this one:

%LET STATE=('AL' or 'CA' or 'NY' or 'WY');

PROC SQL;

   CREATE TABLE My_Table AS

   SELECT DISTINCT

        t1.Var1,

        t1.Var2,

        t1.Var3,

        t.State

    FROM My_Data t1

    WHERE t1.State = &State.

QUIT;

[Tom]

This has nothing to do with macros since your code is not using any macros.

The problem does not really have anything to do with the macro variable your code is using.

Instead the issue is just understanding what SAS syntax to use for selecting multiple values.

Use the IN operator.

WHERE t1.State in ('AL' 'CA' 'NY' 'WY')

Now that you know what SAS code you want to run it is easy to know what type of values you want to put in your macro variable.

%let start= ('AL' 'CA' 'NY' 'WY');

[PaigeMiller]

Normally, SAS will tell you if the code is correct or not, and quickly. No need to ask us.

But, let me point out something. When you run code with macro variables, the value of the macro variable is inserted into the code, replacing the macro variable itself. So your code will look like this:

WHERE t1.State = ('AL' or 'CA' or 'NY' or 'WY')

And so my question back to you is: will this work? Is this valid legal working SAS code? You need to ask yourself that question every time you use macro variables. I will save you some time and indicate that this doesn't work. What is the proper syntax in PROC SQL to check to see if t1.State is 'AL' or 'CA' or 'NY' or 'WY'? Write out the proper working syntax without the macro variable. Then you should be able to produce code that works with a macro variable.

I give you this advice because I see far too many people running into the same problem, they don't have working SAS code without macro variables and so it will never work with macro variables either. Most people ignore this advice — that's a huge mistake — don't be one of those people. Follow the advice, get the code to work without macro variables first, and you efforts at writing code with macro variables will improve dramatically.

[Patrick · Accepted solution]

Others explained it already so just for absolute clarity:

The EQUAL operator is only suitable if you compare against a single value. If you want to compare against a list of values then you need to use the IN operator.

But then with the IN operator it's just a list of values without any "embedded" logic operators like AND or OR.

You could also express the list as a series of OR conditions using and EQUAL operator - same result but more coding.

PROC SQL;
  CREATE TABLE My_Table AS
    SELECT DISTINCT
      t1.Var1,
      t1.Var2,
      t1.Var3,
      t.State
    FROM My_Data t1
      WHERE t1.State ='AL' or t1.state='CA' or t1.state='NY' or t1.state='WY'
  ;
QUIT