hello,
I'm new with sas. Can someone to help me with this simple script looping or macro?
proc sql;
connect using oracle;
create table temp_oracle as
select * from connection to oracle
(
select * from table a
where variable in(&split1.)
or variable in(&split2.)
or variable in(&split3.)
or variable in(&split4.)
or variable in(&split5.)
or variable in(&split6.) ….
)
;quit;
I take it you want to reduce the typing to get this repetitive code:
variable in(&split1.)
or variable in(&split2.)
or variable in(&split3.)
or variable in(&split4.)
or variable in(&split5.)
or variable in(&split6.) ….
Here's a macro that generates the code for you:
%macro splits (n_splits);
%local k;
%do k = 1 %to &n_splits;
variable in (&&split&k)
%if &k < &n_splits %then or;
%end;
%mend splits;
You would use that macro to replace all the typing:
(
select * from table a
where %splits (6)
)
Use whatever number is appropriate in parentheses, it doesn't have to be 6.
Hello,
Need more information on how many time you need this loop and on what basis. You probably can create that condition using a dataset or write a logic to put the whole condition as a macro and call it in pass-through.
Loop to be outside the pass-through.
%let condition=variable in(&split1.)
or variable in(&split2.)
or variable in(&split3.)
or variable in(&split4.)
or variable in(&split5.)
or variable in(&split6.) ;
proc sql;
connect using oracle;
create table temp_oracle as
select * from connection to oracle
(
select * from table a
where &condition
)
;quit;
Provide more information so to create a logic for creating the condition.
Hi @Alathuythu
Do you want to loop through the "WHERE" clause as follows?
/* First iteration */
proc sql;
connect using oracle;
create table temp_oracle as
select * from connection to oracle
(
select * from table a where variable in (&split1.)
)
;quit;
/* Second iteration */
proc sql;
connect using oracle;
create table temp_oracle as
select * from connection to oracle
(
select * from table a where variable in (&split2.)
)
;quit;
/* etc. */
I take it you want to reduce the typing to get this repetitive code:
variable in(&split1.)
or variable in(&split2.)
or variable in(&split3.)
or variable in(&split4.)
or variable in(&split5.)
or variable in(&split6.) ….
Here's a macro that generates the code for you:
%macro splits (n_splits);
%local k;
%do k = 1 %to &n_splits;
variable in (&&split&k)
%if &k < &n_splits %then or;
%end;
%mend splits;
You would use that macro to replace all the typing:
(
select * from table a
where %splits (6)
)
Use whatever number is appropriate in parentheses, it doesn't have to be 6.
@Alathuythu wrote:
hello,
I'm new with sas. Can someone to help me with this simple script looping or macro?
proc sql;
connect using oracle;
create table temp_oracle as
select * from connection to oracle
(
select * from table awhere variable in(&split1.)
or variable in(&split2.)
or variable in(&split3.)
or variable in(&split4.)
or variable in(&split5.)
or variable in(&split6.) ….
);quit;
Your code currently does not define (or use) any macros.
It is referencing 6 macro variables. But you did not show how you are defining those macro variables.
What is it you need help with?
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.