1

In a SAS script I have a macro variable which is later used in an SQL in statement in a PROC SQL step.

%let my_list = (1,2,3);

proc sql;
  select *
  from my_table
  where var1 in &my_list.
;
quit;

This works fine, but I need some flexibility and also want to be able to select ALL lines without changing the SQL code itself, but just the macro variable.

Is there a trick to specifiy the macro variable so it selects ALL lines still using the IN operator? (avoiding a subquery solution that fills all possible values in the macro variable)

Mark Heckmann
  • 10,943
  • 4
  • 56
  • 88

1 Answers1

2

You could change your code to 

%let where_clause = var1 in (1,2,3);

proc sql;
  select *
  from my_table
  where &where_clause
;
quit;

And change the macro variable to %let where_clause = 1=1; in order to select all lines.

%let where_clause = 1=1;

proc sql;
  select *
  from my_table
  where &where_clause
;
quit;

OR, if you are adamant about keeping your code unchanged, you could simply change the macro variable as follows in order for your where clause to always be true:

%let my_list = (1) or 1=1;

proc sql;
  select *
  from my_table
  where var1 in &my_list
;
quit;

(dirty but gets the job done)

user2877959
  • 1,792
  • 1
  • 9
  • 14
  • nice straightforward ideas. I keep thinking too much in procedural programming logic like in R, not the SAS "find and replace" macro approach. Thanks. – Mark Heckmann Aug 02 '17 at 13:04