How to create loop or macro inside pass through query?

[Alathuythu]

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;

[SuryaKiran]

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. 

[ed_sas_member]

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. */

[Astounding · Accepted solution]

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]

This is work perfectly. Thank you very much!

[Tom]

---

@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 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;

---

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?