Dear SAS Community,
I have macro variables generated from &var1 to &var50. I want to query them in PROC SQL and list them in where clause (like &var1 or &var2 .....or &var50). How can I generate a macro to list all of these 50 variables with 'OR' separation to list.
I want to have a single macro to list all of these 50 variables like (%let new= &var1 or &var2 .... &50).
In data step we have : operator to list (like var: will get all of the variables whose prefix changes).
example code:
proc sql;
create table new_table as
select *
from want
where number in (&new); quit;
thanks
%macro dothis;
proc sql;
create table new_table as
select * from want
where number in (%do i=1 %to 50; &&var&i %if &i<50 %then %str(,); %end;);
quit;
%mend;
%dothis
Please note: the syntax you are using
where number in (&var1 or &var2 or ...)
doesn't seem correct as the OR does not belong in the IN () as far as I know. SQL works fine with commas or spaces as the separator, I have programmed it to have a comma as the separator.
And I agree with @Kurt_Bremser , you would be better off using some other structure here, if possible, as he suggests.
What do these macro variables contain? Values or variable names? And in which form? And how many?
If you get these values from a dataset, it's much better to use a sub-select.
%macro dothis;
proc sql;
create table new_table as
select * from want
where number in (%do i=1 %to 50; &&var&i %if &i<50 %then %str(,); %end;);
quit;
%mend;
%dothis
Please note: the syntax you are using
where number in (&var1 or &var2 or ...)
doesn't seem correct as the OR does not belong in the IN () as far as I know. SQL works fine with commas or spaces as the separator, I have programmed it to have a comma as the separator.
And I agree with @Kurt_Bremser , you would be better off using some other structure here, if possible, as he suggests.
Note the IN operator does NOT need commas separating the items in the list. Spaces work just fine.
It may help a lot to show an example of the SQL query that works without any macro coding or variables.
Then tell us which part of the query you want the macro values used.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.