BookmarkSubscribeRSS Feed
sassygrl
Calcite | Level 5
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;
3 REPLIES 3
sassygrl
Calcite | Level 5
<= 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
sassygrl
Calcite | Level 5
My apologies, it is not allowing me to post any more of my code 😞
sassygrl
Calcite | Level 5
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 792 views
  • 0 likes
  • 1 in conversation