BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
altijani
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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.

Patrick_0-1652784956038.png

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

 

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

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
Diamond | Level 26

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.

--
Paige Miller
Patrick
Opal | Level 21

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.

Patrick_0-1652784956038.png

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

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 410 views
  • 2 likes
  • 4 in conversation