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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.