We can use the Magical Keyword "INTO:" in PROC SQL even if we make query from Teradata.

“INTO:” host-variable in PROC SQL is a powerful tool. It simplifies programming code while

minimizing the risk of typographical errors.

SQL INTO: creates one or more macro variables, based on the results of a SELECT statement.

proc sql;

select compress("'"|| put( ruler_date -day(ruler_date)+1, yymmddd10.) ||"'")  ,
          compress("'"|| put(intnx('month',ruler_date-day(ruler_date)+1,2),yymmddd10.) ||"'") ,
          compress("'"|| put(intnx('month',ruler_date-day(ruler_date)+1,4),yymmddd10.) ||"'") ,
          count(*)  as counter,
          put(100* year(intnx('month',ruler_date-day(ruler_date)+1,2))

       + month(intnx('month',ruler_date-day(ruler_date)+1,2)),6.0)

      into :taar_first separated BY "+",

            :taar_last separated BY "+",

            :taar_last_l separated BY "+",

            :counter,

           :name  separated BY "+" 

from teraprod.VBM151_PRV_RULER_DTL_COD_DATES

where time_code=2

and ruler_date>'1oct2005'd

and ruler_date<'1feb2007'd

order by ruler_date;

quit;

%put &taar_last;

%put &taar_first;

%put &taar_last_l;

%put &counter; 

options mprint mlogic;

Application of the “INTO:” Host-Variable

%macro peulot;

data nohesh.netunim_all;delete ;run;

%do i=1 %to &counter;

proc sql;

connect to teradata

(user=xxx password=123 tdpid=DWPROD);

create table nohesh.peulot_%scan(&name,&i,+) as

select * from connection to teradata

(select branch_cust_ip,

count(*) as peulot

from bo_vall.V0500_1_FINANCIAL_EVENT as a,

bo_vall.VBM845_FINANCIAL_EVENT_CUST as b

where event_start_date ge %scan(&first_d,&i,+)

and event_start_date lt %scan(&last_d,&i,+)

and a.event_id=b.event_id

group by 1 );

disconnect from teradata;

quit;

data nohesh.netunim_all;

set nohesh.netunim_all

nohesh.peulot_%scan(&name,&i,+);

run;

%end;

%mend;

%peulot;