for more explanation: My DBA gives me that code to execute in sas: %Include "/SAS92/data/segchurn/sas2/autoexec_dev.sas" ; Proc Sql ; Connect To Oracle (User = &UserOracle Pass = &PassOracle Path = &PathOracle) ; execute (drop table bi_prod.tbl_act_sas) by oracle ; execute (create table bi_prod.tbl_act_sas as select cod_linea , fecha_alta from DW_D_LINEA , bi_prod.param_date_act a where cod_sistema='2' and flag_reutilizado= 0 and fecha_alta >= dd and fecha_alta <df+1 ) By oracle ; execute(truncate table bi_prod.tbl_rec_sas ) by oracle ; execute (insert into bi_prod.tbl_rec_sas (select SUM(DW_F_RECARGAS.IMPORTE_RECARGA) mnt_recharge,cod_linea from dw_f_recargas , DW_D_ORIGEN_RECARGA,bi_prod.param_date_analyse a where DW_D_ORIGEN_RECARGA.ID_ORIGEN_RECARGA=DW_F_RECARGAS.ID_ORIGEN_RECARGA AND dw_f_recargas.cod_sistema='2' and DW_D_ORIGEN_RECARGA.DES_ORIGEN_RECARGA IN ('Call Center', 'BTU', 'IN', 'ETU', 'E-payment') and cod_linea in (select cod_linea from bi_prod.tbl_act_sas) and id_dia >= dd and id_dia <df+1 group by cod_linea) ) by oracle ; execute(commit) by oracle; execute (truncate table bi_prod.tbl_out_sas) by oracle ; execute (insert into bi_prod.tbl_out_sas ( select SUM(dw_f_prepago.importe) mnt_out,telefono_origen from dw_f_prepago ,bi_prod.param_date_analyse a where telefono_origen in (select cod_linea from bi_prod.tbl_act_sas) and id_dia >= dd and id_dia <df+1 group by telefono_origen ) ) by oracle ; execute(commit) by oracle ; execute (truncate table bi_prod.tbl_nav_sas) by oracle ; execute (insert into bi_prod.tbl_nav_sas ( select SUM(volume_up+volume_down) vol,num_linea from dw_f_navigation ,bi_prod.param_date_analyse a where cod_sistema='2' and num_linea in (select cod_linea from bi_prod.tbl_act_sas) and id_dia >= dd and id_dia <df+1 group by num_linea ) by oracle ; execute(commit) ; execute (truncate table bi_prod.tbl_inter_sas) by oracle ; execute ( insert into bi_prod.tbl_inter_sas ( select SUM(ingresos) mou_in,telefono_destino from dw_f_interconexion ,bi_prod.param_date_analyse a where cod_sistema='2' and telefono_destino in (select cod_linea from bi_prod.tbl_act_sas) and id_dia >= dd and id_dia <df+1 group by telefono_destino ) ) by oracle ; execute(commit) ; Create Table sasuser.analyse_identif As Select * From Connection To Oracle ( select a.cod_linea , a.fecha_alta date_act ,b.vol ,c.mou_in,d.mnt_out mou_out,e.mnt_recharge from bi_prod.tbl_act_sas a left outer join bi_prod.tbl_nav_sas b on a.cod_linea = b.num_linea left outer join bi_prod.tbl_inter_sas c on a.cod_linea = c.telefono_destino left outer join bi_prod.tbl_out_sas d on a.cod_linea = d.telefono_origen left outer join bi_prod.tbl_rec_sas e on a.cod_linea = e.cod_linea ) ; Disconnect From Oracle ; Quit ; But it takes a lot (2 days and it does not finish) so tried to optomize it, the idea was to segment the code and run those of same step in parallel and it wored. Now i want to automate that. this my new code: /* création des tables 1,..,4 */ execute (create table bi_prod.tbl_nav_sas_1 as select * from bi_prod.tbl_nav_sas) by oracle; execute (create table bi_prod.tbl_nav_sas_2 as select * from bi_prod.tbl_nav_sas) by oracle; execute (create table bi_prod.tbl_nav_sas_3 as select * from bi_prod.tbl_nav_sas) by oracle; execute (create table bi_prod.tbl_nav_sas_4 as select * from bi_prod.tbl_nav_sas) by oracle; execute (create table bi_prod.tbl_nav_sas_join_1 as select * from bi_prod.tbl_nav_sas) by oracle; execute (create table bi_prod.tbl_nav_sas_join_2 as select * from bi_prod.tbl_nav_sas) by oracle; /* 1 */ execute (truncate table bi_prod.tbl_nav_sas_1) by oracle ; execute (insert into bi_prod.tbl_nav_sas_1 ( select SUM(volume_up+volume_down) vol, num_linea from dw_f_navigation ,bi_prod.param_date_analyse a where cod_sistema='2' group by num_linea )) by oracle ; execute(commit) by oracle; /* 2 */ execute (truncate table bi_prod.tbl_nav_sas_2) by oracle ; execute (insert into bi_prod.tbl_nav_sas_2 ( select SUM(volume_up+volume_down) vol, num_linea from dw_f_navigation ,bi_prod.param_date_analyse a where num_linea in (select cod_linea from bi_prod.tbl_act_sas) group by num_linea )) by oracle ; execute(commit) by oracle; /* 3 */ execute (truncate table bi_prod.tbl_nav_sas_3) by oracle ; execute (insert into bi_prod.tbl_nav_sas_3 ( select SUM(volume_up+volume_down) vol, num_linea from dw_f_navigation ,bi_prod.param_date_analyse a where id_dia >= dd group by num_linea )) by oracle ; execute(commit) by oracle; /* 4 */ execute (truncate table bi_prod.tbl_nav_sas_4) by oracle ; execute (insert into bi_prod.tbl_nav_sas_4 ( select SUM(volume_up+volume_down) vol, num_linea from dw_f_navigation ,bi_prod.param_date_analyse a where id_dia <df+1 group by num_linea )) by oracle ; execute(commit) by oracle; /* jointure */ /* 1 */ execute (truncate table bi_prod.tbl_nav_sas_join_1) by oracle ; execute(insert into bi_prod.tbl_nav_sas_join_1 (SELECT * FROM bi_prod.tbl_nav_sas_1 INNER JOIN bi_prod.tbl_nav_sas_2 ON bi_prod.tbl_nav_sas_1.num_linea = bi_prod.tbl_nav_sas_2.num_linea)) by oracle; execute(commit) by oracle; /* 2 */ execute (truncate table bi_prod.tbl_nav_sas_join_2) by oracle ; execute(insert into bi_prod.tbl_nav_sas_join_2 (SELECT * FROM bi_prod.tbl_nav_sas_3 INNER JOIN bi_prod.tbl_nav_sas_4 ON bi_prod.tbl_nav_sas_3.num_linea = bi_prod.tbl_nav_sas_4.num_linea)) by oracle; execute(commit) by oracle; /* finale */ execute (truncate table bi_prod.tbl_nav_sas) by oracle ; execute (insert into bi_prod.tbl_nav_sas ( select * from bi_prod.tbl_nav_sas_join_1 inner join bi_prod.tbl_nav_sas_join_2 on bi_prod.tbl_nav_sas_join_1.num_linea = bi_prod.tbl_nav_sas_join_1.num_linea) group by num_linea ) by oracle ; execute(commit) by oracle; Now i can execute my task in 12 hours earlier.
... View more