topic Re: "Efficientize" my SQL code in SAS Procedures

"Efficientize" my SQL code

sassygrl — Fri, 21 Aug 2009 17:31:56 GMT

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;

Re: "Efficientize" my SQL code

sassygrl — Fri, 21 Aug 2009 17:34:07 GMT

<= 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; Message was edited by: sassygrl

Re: "Efficientize" my SQL code

sassygrl — Fri, 21 Aug 2009 17:35:35 GMT

My apologies, it is not allowing me to post any more of my code 😞

Re: "Efficientize" my SQL code

sassygrl — Fri, 21 Aug 2009 17:39:24 GMT

I think it doesn't like the symbols. Here's the rest:

le calculated shipdt le '11JUL2009'd
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');

Quit;