Using any values inside IN statement

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

Using any values inside IN statement

Hi all

I have the following scenario:

%LET NAMES =  ('Alfred','Alice','Barabara');

CREATE TABLE TEST AS SELECT NAME FROM SASHELP.CLASS WHERE NAME IN &NAMES;

In the above statement, I give the condition inside a macro variable and use it in a IN statement. But many a times, I would need entire set of names rather than restricting it to few names. I need to have the flexibility of restricting the valeus as well as allowing all values to be slelected with a minimal code change. Say, comment one %LET statement and using another.

Is there any wya by which IN statement picks up all possible values of a variable? Commenting out the WHERE condition may not be a practical solution as there are multiple CREATE TABLE statements in my code.

Advance thanks for your inputs.


Accepted Solutions
Solution
‎12-18-2013 05:57 PM
Super User
Super User
Posts: 6,502

Re: Using any values inside IN statement

Easiest thing might be to move the variable name into the macro variable.

%LET NAMES =  ('Alfred','Alice','Barabara');

%LET WHERE = name in &names ;

CREATE TABLE TEST AS SELECT NAME FROM SASHELP.CLASS WHERE &where ;

Then when you want to NOT subset you could change the WHERE variable.


%LET WHERE = 1 ;


If you want to avoid macro logic (and hence the need to create a macro) you might look at using the IFC() function instead.


%LET NAMES =  * ;

%LET WHERE = %sysfunc(ifc("&names"="*",1,name in &names)) ;


View solution in original post


All Replies
Super Contributor
Posts: 339

Re: Using any values inside IN statement

First, if you have very large IN statements, I would strongly recommend learning to use hash tables as the IN operator is extremely inefficient.

However, there are many ways you can achieve this.

First, you could use subqueries that return a single column of values in SQL

proc sql;

     select *

     from fileA

     where name in (select name from fileB /* could also be file A again */ /* where insert conditions that allow you to subset all the names or remove where statement if you have your long long list of names in a different file*/)

     ;

quit;

Similar alternative although that allows you to use the in operator also in a data step instead of proc sql:

proc sql;

     select quote(name)

     into :names separated by ' , '

     from fileB

     /* where conditioning if necessary */

     ;

quit;

This creates a macro variable NAMES as did your %let statement although with up to 32767 characters to create the list. you use it like

where name in (&names.)

that is, you have to add parantheses. If 32767 is too few, then you can use distinct macro variables and create a macro to generate a huge IN statement and it is also usable with data step but it is still extremely ineficient. At that point if possible you'd be far better off using hash tables or joins (the join has to sort the datasets which may seem long at first but the IN operator passes through all values one by one for every single row of data so if the number of values in the IN operator is large, sorting data and joining is a better alternative).

Contributor
Posts: 50

Re: Using any values inside IN statement

Hi Vince

Thanks for your response. I may not not have large number of IN statement. I might either have 3 or 4 values in a IN statement for some kind of routine debugging validation. In many a times, I have no necessity to restrict the value at all.

Like,

CREATE TABLE TEST AS SELECT NAME FROM SASHELP.CLASS;

The requirement is to have ability to restrict the data / use it without restriction with a minimal code change.

Solution
‎12-18-2013 05:57 PM
Super User
Super User
Posts: 6,502

Re: Using any values inside IN statement

Easiest thing might be to move the variable name into the macro variable.

%LET NAMES =  ('Alfred','Alice','Barabara');

%LET WHERE = name in &names ;

CREATE TABLE TEST AS SELECT NAME FROM SASHELP.CLASS WHERE &where ;

Then when you want to NOT subset you could change the WHERE variable.


%LET WHERE = 1 ;


If you want to avoid macro logic (and hence the need to create a macro) you might look at using the IFC() function instead.


%LET NAMES =  * ;

%LET WHERE = %sysfunc(ifc("&names"="*",1,name in &names)) ;


N/A
Posts: 1

Re: Using any values inside IN statement

I am using the following solution for a macro variable containing multiple text values to be used in an IN statement:

proc sql noprint;

Select distinct compress(''''||Text_Value||'''') as Text_Value into :Macro_Variable separated by ','

From Value_Table;

Quit;

this will produce a variable containing the following string - 'a','b','c',.....

allowing:

data test1;

     set test2;

If Test_Value in (&Macro_Variable.) then do;

     etc...

Super User
Posts: 9,687

Re: Using any values inside IN statement

Code: Program

%LET NAMES =  Alfred Alice Barabara;
PROC SQL;
CREATE TABLE TEST AS
SELECT NAME
  FROM SASHELP.CLASS
   WHERE SYMGET('NAMES') CONTAINS STRIP(NAME) ;
QUIT;
Super User
Super User
Posts: 6,502

Re: Using any values inside IN statement

Great idea.  INDEXW() function might work better than CONTAINS to prevent matching FRED to ALFRED.

Super User
Posts: 9,687

Re: Using any values inside IN statement

Tom,

Yeah. That would be better. I used to overlook it .

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 721 views
  • 5 likes
  • 5 in conversation