topic Re: How to create loop or macro inside pass through query? in SAS Programming

How to create loop or macro inside pass through query?

Alathuythu — Mon, 25 Nov 2019 18:48:20 GMT

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;

Re: How to create loop or macro inside pass through query?

SuryaKiran — Mon, 25 Nov 2019 18:55:13 GMT

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.

Re: How to create loop or macro inside pass through query?

ed_sas_member — Mon, 25 Nov 2019 18:55:17 GMT

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

Re: How to create loop or macro inside pass through query?

Astounding — Mon, 25 Nov 2019 20:32:45 GMT

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.

Re: How to create loop or macro inside pass through query?

Tom — Mon, 25 Nov 2019 20:34:55 GMT

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

Re: How to create loop or macro inside pass through query?

Alathuythu — Tue, 26 Nov 2019 04:14:33 GMT

This is work perfectly. Thank you very much!