topic Re: How to generate sequencial macro variable names and how to list them short in sas in SAS Programming

How to generate sequencial macro variable names and how to list them short in sas

buddha_d — Wed, 15 Apr 2020 11:19:43 GMT

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

Re: How to generate sequencial macro variable names and how to list them short in sas

Kurt_Bremser — Wed, 15 Apr 2020 11:31:11 GMT

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.

Re: How to generate sequencial macro variable names and how to list them short in sas

PaigeMiller — Wed, 15 Apr 2020 11:36:15 GMT

%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.

Re: How to generate sequencial macro variable names and how to list them short in sas

ballardw — Wed, 15 Apr 2020 14:46:28 GMT

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.

Re: How to generate sequencial macro variable names and how to list them short in sas

Tom — Wed, 15 Apr 2020 15:15:14 GMT

Note the IN operator does NOT need commas separating the items in the list. Spaces work just fine.

Re: How to generate sequencial macro variable names and how to list them short in sas

buddha_d — Wed, 15 Apr 2020 20:33:08 GMT

I can't list the code of macro variables I am creating, but I needed a way to list them. Since there are so many and I can't really post the original code. Thanks for your response. I appreciate it

Re: How to generate sequencial macro variable names and how to list them short in sas

buddha_d — Wed, 15 Apr 2020 20:33:25 GMT

thanks for your input

Re: How to generate sequencial macro variable names and how to list them short in sas

buddha_d — Wed, 15 Apr 2020 20:33:54 GMT

Thanks for your inputs ballardw.

Re: How to generate sequencial macro variable names and how to list them short in sas

buddha_d — Wed, 15 Apr 2020 20:34:37 GMT

Thanks PaigeMiller. It worked like Charm. I really appreciate it.