Hi. I have the following series of queries that run as part of a macro loop, once for each ZIP Code. And there are 68 ZIP Codes! The data is huge so each step can take a couple of hours per ZIP Code. Due to the huge size of the data the datasets ods_bi_recon_selected_mp and ods_iv_recon_selected_mp are created with records from only 1 ZIP Code at a time. Then each in the series of the queries is executed against these datasets. Then the process repeats for each of the remaining ZIPs. I'm curious if anyone sees opportunities for improving efficiency of these individual queries or in executing all the queries as a whole? Any suggestions would be greatly appreciated. /*Create IV dataset*/ %PUT CHECK: CREATE IV DS FOR &ZIP5; proc sql; connect to oracle as db (user=&orauser password=&orapass path="ivasprd"); create table ods_iv_recon_selected_mp as select * from connection to db ( select * from ivas.ods_iv_recon_selected_mp where imb_dlvry_zip_5=&ZIP5_QUOTED ); disconnect from db; quit; /*Create BIDS dataset*/ %PUT CHECK: CREATE BIDS DS FOR &ZIP5; proc sql; connect to oracle as db (user="myuser" password="mypw" path="ibscrprd"); create table ods_bi_recon_selected_mp as select * from connection to db ( select * from imapsscr.ods_bi_recon_selected_mp where imb_dlvry_zip_5=&ZIP5_QUOTED ); disconnect from db; quit; The series of queries are: /* RULE: PIECES MISSING IN IV QUERY */
%PUT CHECK: RULE: PIECES MISSING IN IV QUERY 999.1;
proc sql;
create table QueryData as
select subpad('PIECES MISSING IN IV',1,58) as RULE_NM length=58,
actual_dlvry_date,
subpad(imb_code,1,31) as IMB_CODE length=31,
999.1 as Rule_Order,
imb_dlvry_zip_5
from ods_bi_recon_selected_mp
where imb_code not in(select imb_code
from ods_iv_recon_selected_mp);
quit;
/* Append datasets to final dataset */
proc append base=QueryData&ZIP5 data=QueryData force;
run;
/* RULE: PIECES MISSING IN BIDS QUERY */
%PUT CHECK: RULE: PIECES MISSING IN BIDS QUERY 999.6;
proc sql;
create table QueryData as
select subpad('PIECES MISSING IN BIDS',1,58) as RULE_NM length=58,
actual_dlvry_date,
subpad(imb_code,1,31) as IMB_CODE length=31,
999.6 as RULE_ORDER,
imb_dlvry_zip_5
from ods_iv_recon_selected_mp
where imb_code not in(select imb_code
from ods_bi_recon_selected_mp);
quit;
/* Append datasets to final dataset */
proc append base=QueryData&ZIP5 data=QueryData force;
run;
/* RULE: VOLUME MATCHING */
/* Volume Matching assumes the records match exactly and have not violated any rules */
%PUT CHECK: RULE: VOLUME MATCHING 999.2;
proc sql;
create table QueryData as
select subpad('VOLUME MATCHING',1,58) as RULE_NM length=58,
actual_dlvry_date,
subpad(imb_code,1,31) as IMB_CODE length = 31,
999.2 as RULE_ORDER,
imb_dlvry_zip_5
from ods_bi_recon_selected_mp
EXCEPT
select 'VOLUME MATCHING' as RULE_NM length=58,
actual_dlvry_date,
imb_code length = 31,
999.2 as RULE_ORDER,
imb_dlvry_zip_5
from QueryData&ZIP5
where rule_order < 997.1
and rule_order is not null
EXCEPT
select 'VOLUME MATCHING' as RULE_NM length=58,
actual_dlvry_date,
imb_code length = 31,
999.2 as RULE_ORDER,
imb_dlvry_zip_5
from ods_bi_recon_selected_mp
where imb_code not in(select imb_code
from ods_iv_recon_selected_mp)
;
quit;
/* Append datasets to final dataset */
proc append base=QueryData&ZIP5 data=QueryData force;
run;
/* RULE: TOTAL BIDS VOLUME SAMPLED */
%PUT CHECK: RULE: TOTAL BIDS VOLUME SAMPLED 999.3;
data QueryData;
length rule_nm $58;
length imb_code $31;
set ods_bi_recon_selected_mp;
RULE_NM='TOTAL BIDS VOLUME SAMPLED';
RULE_ORDER=999.3;
keep rule_nm actual_dlvry_date imb_code rule_order imb_dlvry_zip_5;
run;
/* Append datasets to final dataset */
proc append base=QueryData&ZIP5 data=QueryData force;
run;
/* RULE: EXCLUDED IN IV INCLUDED IN BIDS */
%PUT CHECK: RULE: EXCLUDED IN IV INCLUDED IN BIDS 999.4;
proc sql;
create table QueryData as
select subpad('EXCLUDED IN IV INCLUDED IN BIDS',1,58) as RULE_NM length=58,
actual_dlvry_date,
imb_code length=31,
999.4 as RULE_ORDER,
imb_dlvry_zip_5
from ods_bi_recon_selected_mp
where excl_sts_code is null
and imb_code in (select imb_code
from ods_iv_recon_selected_mp
where excl_sts_code is not null);
quit;
/* Append datasets to final dataset */
proc append base=QueryData&ZIP5 data=QueryData force;
run;
/* RULE: INCLUDED IN IV EXCLUDED IN BIDS */
%PUT CHECK: RULE: INCLUDED IN IV EXCLUDED IN BIDS 999.5;
proc sql;
create table QueryData as
select subpad('INCLUDED IN IV EXCLUDED IN BIDS',1,58) as RULE_NM length=58,
actual_dlvry_date,
imb_code length=31,
999.5 as RULE_ORDER,
imb_dlvry_zip_5
from ods_bi_recon_selected_mp
where excl_sts_code is not null
and imb_code in (select imb_code
from ods_iv_recon_selected_mp
where excl_sts_code is null);
quit;
/* Append datasets to final dataset */
proc append base=QueryData&ZIP5 data=QueryData force;
run;
... View more
