Yes, I made up that word 🙂 I'm just curious if this program could be improved for noticeably better performance? I'm using a rather archaic machine on rather large data.
I apologize in advance if copy/paste doesn't preserve my indenting. Thanks!
proc sql;
/*** All customers who ordered a 3-on intro pack b/t 30Dec2007 and 11Jul2009 ***/
create table outdata.INTROPACKS as
select a.sales_brand_bsnss_id,
f.current_corp_group_id,
a.request_id,
a.request_detail_line_id,
a.sales_brand_cd,
b.sales_brand_cd as bbrand_cd,
a.part_of_kit_cd,
a.credit_ind,
a.source_mkt,
a.software_cd,
a.booked_amt,
a.order_discount_amt,
a.discount_amt,
a.rev,
datepart(a.shipped_item_status_dt) as shipdt format date9.,
datepart(a.shipped_item_status_dt)+30 as shipplus30 format date9.,
datepart(a.shipped_item_status_dt)+60 as shipplus60 format date9.,
datepart(a.shipped_item_status_dt)+90 as shipplus90 format date9.,
datepart(a.billed_item_status_dt) as billdt format date9.,
a.prod_id,
c.prod_line_cd,
d.prod_line_cd_desc,
case when b.promo_method_cd^='' then b.promo_method_cd
when b.promo_method_cd='' and b.sales_brand_bsnss_id^='' then 'm'
else ''
end as promo_method_cd,
e.promo_method_cd_desc
from SASWORK.weekly_line_item_wk28 a
left join SASOWNER.SBS_REQUEST_T b
on (a.sales_brand_cd=b.sales_brand_cd
or (a.sales_brand_cd in ('A','W') and b.sales_brand_cd in ('A','W')))
and a.request_id = b.request_id
/* and a.sales_brand_bsnss_id = b.sales_brand_bsnss_id */
left join (select distinct prod_id, prod_line_cd
from SASOWNER.SBS_PROD_ID_T) c
on a.prod_id = c.prod_id
left join SASOWNER.SBS_PROD_LINE_T d
on c.prod_line_cd = d.prod_line_cd
left join SASOWNER.SBS_REF_PROMO_METHOD_CD_T e
on b.promo_method_cd = e.promo_method_cd
left join SASOWNER.SBS_PERKEY_XREF_T f
on a.sales_brand_bsnss_id = f.sales_brand_bsnss_id
where '30DEC2007'd <= calculated shipdt <= '11JUL2009'd
/*and a.part_of_kit_cd ^= 'C'*/
and a.no_charge_ind ^= 'Y'
and a.cancelled_item_status_dt = .
and a.sales_brand_cd in ('A','W','E','I','P')
and a.source_mkt ^in ('BRP Cash Management Dev','Cash Management Dev.')
and calculated promo_method_cd ^in ('G','7','m')
having substr(a.prod_id,1,5) in ('53251' '53252' '53253' '53254' '53151' '53152' '53153' '53154');
/*** Unique list of SBBID who ordered intro pack ***/
create table LIST as
select distinct sales_brand_bsnss_id, current_corp_group_id
from outdata.INTROPACKS;
/*** Above customers who also ordered a laser check product ***/
create table outdata.LASERCHECKS as
select z.sales_brand_bsnss_id,
z.current_corp_group_id,
a.request_id,
a.request_detail_line_id,
a.sales_brand_cd,
b.sales_brand_cd as bbrand_cd,
a.part_of_kit_cd,
a.credit_ind,
a.source_mkt,
a.software_cd,
a.booked_amt,
a.order_discount_amt,
a.discount_amt,
a.rev,
datepart(a.shipped_item_status_dt) as shipdt format date9.,
datepart(a.billed_item_status_dt) as billdt format date9.,
a.prod_id,
c.prod_line_cd,
d.prod_line_cd_desc,
case when b.promo_method_cd^='' then b.promo_method_cd
when b.promo_method_cd='' and b.sales_brand_bsnss_id^='' then 'm'
else ''
end as promo_method_cd,
e.promo_method_cd_desc
from LIST z left join SASWORK.weekly_line_item_wk28 a
on z.sales_brand_bsnss_id = a.sales_brand_bsnss_id
left join SASOWNER.SBS_REQUEST_T b
on (a.sales_brand_cd=b.sales_brand_cd
or (a.sales_brand_cd in ('A','W') and b.sales_brand_cd in ('A','W')))
and a.request_id = b.request_id
/* and a.sales_brand_bsnss_id = b.sales_brand_bsnss_id */
left join (select distinct prod_id, prod_line_cd
from SASOWNER.SBS_PROD_ID_T) c
on a.prod_id = c.prod_id
left join SASOWNER.SBS_PROD_LINE_T d
on c.prod_line_cd = d.prod_line_cd
left join SASOWNER.SBS_REF_PROMO_METHOD_CD_T e
on b.promo_method_cd = e.promo_method_cd
where '30DEC2007'd <= calculated billdt <= '11JUL2009'd
/*and a.part_of_kit_cd ^= 'C'*/
and a.no_charge_ind ^= 'Y'
and a.cancelled_item_status_dt = .
and c.prod_line_cd = '10'
and a.sales_brand_cd in ('A','W','E','I','P')
and a.source_mkt ^in ('BRP Cash Management Dev','Cash Management Dev.')
and calculated promo_method_cd ^in ('G','7','m');
quit;
