I recently completed a join in SAS, but now there are duplicate records. Here's my code below. How can I go about cleaning up the duplicates? In NODUPKEY an option or the best way to go?
OPTIONS NODBIDIRECTEXEC;
libname Lindsay '/sas_env/empl/scm/SCM_SAS_PROD/DEV';
libname Tables '/sas_env/empl/scm/SCM_SAS_PROD/DEV';
/*This section provides the FDOS report*/
proc sql;
connect to db2 (database=SASADR7 user=BRIDGEK1 password=xxxxxxx);
CREATE TABLE Work.FDOS_Report AS
SELECT DISTINCT *
FROM CONNECTION TO db2(
select distinct dos.distribution_center_nbr
,DOS.fivewk
,mo001p.merch_opstudy_desc
,cl112p.vendor_number
,cl112p.safety_stock_qty
,ve001p.vendor_name1
,cast (dos.planogram_link_nbr as float) "PLN_NBR"
,it005p.item_description
,sd5p.bdm
,it830p.item_lifecycle_status
,DOS.reg_ioh
,DOS.Distr_IOH
,q2.net_open_order
,case when DOS.fivewk <=0 then 999999
when dos.fivewk is null then 999999
else (DOS.reg_ioh*7)/DOS.fivewk end as "IOH DOS"
,case when DOS.fivewk <=0 then 999999
when dos.fivewk is null then 999999
else (q2.net_open_order*7)/DOS.fivewk end as "OO DOS"
,case when DOS.fivewk <=0 then 999999
when dos.fivewk is null then 999999
else (DOS.reg_ioh*7+q2.net_open_order*7)/DOS.fivewk end as "IOH OO DOS"
,case when cl113.wk1<=0 then 999999
when DOS.reg_ioh<=cl113.wk1 then DOS.reg_ioh*7/cl113.wk1
when DOS.reg_ioh<=cl113.wk2 then 7+DOS.reg_ioh*7/cl113.wk2
when DOS.reg_ioh<=cl113.wk3 then 14+DOS.reg_ioh*7/cl113.wk3
when DOS.reg_ioh<=cl113.wk4 then 21+DOS.reg_ioh*7/cl113.wk4
when DOS.reg_ioh<=cl113.wk5 then 28+DOS.reg_ioh*7/cl113.wk5
when DOS.reg_ioh<=cl113.wk6 then 35+DOS.reg_ioh*7/cl113.wk6
when DOS.reg_ioh<=cl113.wk7 then 42+DOS.reg_ioh*7/cl113.wk7
when DOS.reg_ioh<=cl113.wk8 then 49+DOS.reg_ioh*7/cl113.wk8
when DOS.reg_ioh<=cl113.wk9 then 56+DOS.reg_ioh*7/cl113.wk9
when DOS.reg_ioh<=cl113.wk10 then 63+DOS.reg_ioh*7/cl113.wk10
when DOS.reg_ioh<=cl113.wk11 then 70+DOS.reg_ioh*7/cl113.wk11
when DOS.reg_ioh<=cl113.wk12 then 77+DOS.reg_ioh*7/cl113.wk12
when DOS.reg_ioh<=cl113.wk13 then 84+DOS.reg_ioh*7/cl113.wk13
else 999 end as FDOS_IOH
,case when cl113.wk1<=0 then 999999
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk1 then (DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk1
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk2 then 7+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk2
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk3 then 14+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk3
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk4 then 21+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk4
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk5 then 28+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk5
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk6 then 35+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk6
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk7 then 42+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk7
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk8 then 49+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk8
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk9 then 56+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk9
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk10 then 63+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk10
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk11 then 70+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk11
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk12 then 77+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk12
when (DOS.reg_ioh+case when q2.net_open_order is null then 0 else q2.net_open_order end)<=cl113.wk13 then 84+(DOS.reg_ioh*7+case when q2.net_open_order is null then 0 else q2.net_open_order end*7)/cl113.wk13
else 999 end as FDOS_IOH_OO_NEW
,cl113.*,cl113b.*
from
(select it156.planogram_link_nbr
,cl136p.distribution_center_nbr
,sum(qty_available_regular)
as reg_ioh
,sum(qty_available_dist) as distr_ioh
,sum(weekly_avg_sales_5) as fivewk
from mrpdlib.cl136p cl136p
join
mrpdlib.it005p it005p
on cl136p.item_number=it005p.item_number
join
(select distinct item_number
,planogram_link_nbr
from mrpdlib.it156p) it156
on cl136p.item_number=it156.item_number
where cl136p.item_number=it156.item_number
and cl136p.item_number=it005p.item_number
and it005p.item_number in(
'113212',
'274968',
'301526',
'329284',
'167644',
'487400',
'487231',
'487399',
'415518',
'494486',
'196700',
'493468',
'491779',
'491780',
'421845',
'477463',
'638389',
'392129',
'390644',
'390642',
'951994',
'392355',
'196673',
'396568',
'300007',
'493789',
'633944',
'493389',
'179941',
'397387',
'481122',
'513220',
'491036',
'491888',
'635908',
'486065',
'557298',
'914508',
'914467',
'638554',
'632877',
'363624',
'456229',
'489458',
'629831',
'629834',
'630561',
'390256',
'633926',
'398944',
'221700',
'473867',
'502887',
'932196',
'509201',
'561668',
'330122',
'453833',
'931980',
'275196',
'155524',
'453836',
'931980',
'901017',
'901018',
'256185',
'630143',
'680778',
'243878',
'650970',
'717001',
'152213',
'915490',
'350559',
'350558',
'350576',
'908434',
'908435',
'907771',
'434774',
'285375',
'285373',
'285374',
'472248',
'917299',
'917237',
'917307',
'387572',
'387571',
'610847',
'609517',
'382710',
'609514',
'609956',
'631780',
'499499',
'632186',
'632185',
'632196',
'820562',
'820563',
'820564',
'820591',
'822075',
'821344',
'600880',
'600878',
'771880',
'913700',
'825886',
'853722',
'902030',
'647583',
'615649',
'587140',
'811884',
'430293',
'367348',
'357849',
'913614',
'901492',
'326804',
'541450',
'381522',
'656123',
'656319',
'850373',
'507236',
'874275',
'874274',
'874219',
'587730',
'808437',
'808441',
'808442',
'808445',
'466353',
'466348',
'466315',
'466312',
'170306',
'170308',
'900865',
'900864',
'175292'
)
and cl136p.distribution_center_nbr in ('88004','88024','88047')
group by it156.planogram_link_nbr,cl136p.distribution_center_nbr) dos
left outer join mrpdlib.it005p it005p
on substr(dos.planogram_link_nbr,6,6) = it005p.item_number
left outer join (select substr(planogram_link_nbr,6,6) item_number,distribution_center_nbr,sum(bdm_store_count) bdm from mrpdlib.sd005p sd005p,(select distinct item_number,planogram_link_nbr from mrpdlib.it156p) it156p
where sd005p.item_number=it156p.item_number group by planogram_link_nbr,distribution_center_nbr) sd5p
on it005p.item_number=sd5p.item_number
and dos.distribution_center_nbr=sd5p.distribution_center_nbr
left outer join mrpdlib.mo001p mo001p
on it005p.merch_opstudy_nbr=mo001p.merch_opstudy_nbr
left outer join mrpdlib.mo002p mo002p
on it005p.prod_categ_code=mo002p.prod_category_code
and it005p.merch_opstudy_nbr=mo002p.merch_opstudy_nbr
left outer join mrpdlib.it830p it830p
on it005p.item_number=it830p.item_number
left outer join
(SELECT
it156.planogram_link_nbr pln,
po1.po_receiving_dc as distribution_center_nbr,
SUM(po2.QTY_ORDERED_REG_ONLIES)+ SUM(po2.QTY_ORDERED_Distr_ONLIES) -sum(TOTAL_RCVD_QTY_ONLIES) as net_open_order
FROM mrpdlib.po001p po1,
mrpdlib.po002p po2,
mrpdlib.it005p it5p,
mrpdlib.ve001p ve1,
mrpdlib.mo001p mo1,
mrpdlib.mo003p cm,
mrpdlib.mo003p dmm,
mrpdlib.mo003p gmm,
mrpdlib.mo003p ISP,
(select distinct item_number,planogram_link_nbr from mrpdlib.it156p) it156
WHERE po1.PURCHASE_ORDER_NUMBER = po2.PURCHASE_ORDER_NUMBER
and po1.vendor_number=ve1.vendor_number
and it5p.merch_opstudy_nbr=mo1.merch_opstudy_nbr
and mo1.mrktg_pos_id=cm.mrktg_pos_id
and cm.mrktg_pos_reports_to_id=dmm.mrktg_pos_id
and dmm.mrktg_pos_reports_to_id=gmm.mrktg_pos_id
and ve1.reorder_buyer_mrktg_pos=isp.mrktg_pos_id
AND po1.PO_TRANSFER_STATUS ='O'
AND po2.PO_TRANSFER_ITEM_STATUS ='O'
and po1.PO_TRANSFER_IND='P'
AND it5p.item_number=po2.item_number
and it5p.item_number=it156.item_number
GROUP BY
it156.planogram_link_nbr,po1.po_receiving_dc
) q2
on dos.planogram_link_nbr=q2.pln
and dos.distribution_center_nbr=q2.distribution_center_nbr
left outer join
(select cl113p.item_number,cl113p.distribution_center_nbr
,sum(Reg_fcst_qty_1+Event_fcst_qty_1) wk1
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2) wk2
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3) wk3
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4) wk4
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5) wk5
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6) wk6
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7) wk7
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8) wk8
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8)+sum(Reg_fcst_qty_9+Event_fcst_qty_9) wk9
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8)+sum(Reg_fcst_qty_9+Event_fcst_qty_9)+sum(Reg_fcst_qty_10+Event_fcst_qty_10) wk10
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8)+sum(Reg_fcst_qty_9+Event_fcst_qty_9)+sum(Reg_fcst_qty_10+Event_fcst_qty_10)+sum(Reg_fcst_qty_11+Event_fcst_qty_11) wk11
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8)+sum(Reg_fcst_qty_9+Event_fcst_qty_9)+sum(Reg_fcst_qty_10+Event_fcst_qty_10)+sum(Reg_fcst_qty_11+Event_fcst_qty_11)+sum(Reg_fcst_qty_12+Event_fcst_qty_12) wk12
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8)+sum(Reg_fcst_qty_9+Event_fcst_qty_9)+sum(Reg_fcst_qty_10+Event_fcst_qty_10)+sum(Reg_fcst_qty_11+Event_fcst_qty_11)+sum(Reg_fcst_qty_12+Event_fcst_qty_12)+sum(Reg_fcst_qty_13+Event_fcst_qty_13) wk13
from mrpdlib.CL113p cl113p,mrpdlib.it005p it005p,
(select item_number,vendor_number,distribution_center_nbr,max(CML_EFFECTIVE_DTE) EFF
from mrpdlib.cl113p cl113p
where inv_per_quarter=1
group by item_number,vendor_number,distribution_center_nbr) Sub_eff_date
where cl113p.CML_EFFECTIVE_DTE=Sub_eff_date.EFF
and cl113p.item_number=Sub_eff_date.item_number
and cl113p.vendor_number=Sub_eff_date.vendor_number
and cl113p.distribution_center_nbr=Sub_eff_date.distribution_center_nbr
and cl113p.item_number=it005p.item_number
and cl113p.inv_per_quarter=1
and cl113p.CML_EFFECTIVE_DTE between (substr('1',1,1) concat substr(char(CURRENT_DATE - 7 days),3,2) concat substr(char(CURRENT_DATE - 7 days),6,2) concat substr(char(CURRENT_DATE - 7 days),9,2))
and (substr('1',1,1) concat substr(char(CURRENT_DATE),3,2) concat substr(char(CURRENT_DATE),6,2) concat substr(char(CURRENT_DATE),9,2))
and cl113p.item_number in(
'113212',
'274968',
'301526',
'329284',
'167644',
'487400',
'487231',
'487399',
'415518',
'494486',
'196700',
'493468',
'491779',
'491780',
'421845',
'477463',
'638389',
'392129',
'390644',
'390642',
'951994',
'392355',
'196673',
'396568',
'300007',
'493789',
'633944',
'493389',
'179941',
'397387',
'481122',
'513220',
'491036',
'491888',
'635908',
'486065',
'557298',
'914508',
'914467',
'638554',
'632877',
'363624',
'456229',
'489458',
'629831',
'629834',
'630561',
'390256',
'633926',
'398944',
'221700',
'473867',
'502887',
'932196',
'509201',
'561668',
'330122',
'453833',
'931980',
'275196',
'155524',
'453836',
'931980',
'901017',
'901018',
'256185',
'630143',
'680778',
'243878',
'650970',
'717001',
'152213',
'915490',
'350559',
'350558',
'350576',
'908434',
'908435',
'907771',
'434774',
'285375',
'285373',
'285374',
'472248',
'917299',
'917237',
'917307',
'387572',
'387571',
'610847',
'609517',
'382710',
'609514',
'609956',
'631780',
'499499',
'632186',
'632185',
'632196',
'820562',
'820563',
'820564',
'820591',
'822075',
'821344',
'600880',
'600878',
'771880',
'913700',
'825886',
'853722',
'902030',
'647583',
'615649',
'587140',
'811884',
'430293',
'367348',
'357849',
'913614',
'901492',
'326804',
'541450',
'381522',
'656123',
'656319',
'850373',
'507236',
'874275',
'874274',
'874219',
'587730',
'808437',
'808441',
'808442',
'808445',
'466353',
'466348',
'466315',
'466312',
'170306',
'170308',
'900865',
'900864',
'175292'
)
group by cl113p.item_number,cl113p.distribution_center_nbr) CL113
on substr(dos.planogram_link_nbr,6,6)=CL113.item_number
and dos.distribution_center_nbr=cl113.distribution_center_nbr
left outer join
(select cl113p.item_number,cl113p.distribution_center_nbr
,sum(Reg_fcst_qty_1+Event_fcst_qty_1) wk1
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2) wk2
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3) wk3
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4) wk4
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5) wk5
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6) wk6
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7) wk7
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8) wk8
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8)+sum(Reg_fcst_qty_9+Event_fcst_qty_9) wk9
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8)+sum(Reg_fcst_qty_9+Event_fcst_qty_9)+sum(Reg_fcst_qty_10+Event_fcst_qty_10) wk10
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8)+sum(Reg_fcst_qty_9+Event_fcst_qty_9)+sum(Reg_fcst_qty_10+Event_fcst_qty_10)+sum(Reg_fcst_qty_11+Event_fcst_qty_11) wk11
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8)+sum(Reg_fcst_qty_9+Event_fcst_qty_9)+sum(Reg_fcst_qty_10+Event_fcst_qty_10)+sum(Reg_fcst_qty_11+Event_fcst_qty_11)+sum(Reg_fcst_qty_12+Event_fcst_qty_12) wk12
,sum(Reg_fcst_qty_1+Event_fcst_qty_1)+sum(Reg_fcst_qty_2+Event_fcst_qty_2)+sum(Reg_fcst_qty_3+Event_fcst_qty_3)+sum(Reg_fcst_qty_4+Event_fcst_qty_4)+sum(Reg_fcst_qty_5+Event_fcst_qty_5)+sum(Reg_fcst_qty_6+Event_fcst_qty_6)+sum(Reg_fcst_qty_7+Event_fcst_qty_7)+sum(Reg_fcst_qty_8+Event_fcst_qty_8)+sum(Reg_fcst_qty_9+Event_fcst_qty_9)+sum(Reg_fcst_qty_10+Event_fcst_qty_10)+sum(Reg_fcst_qty_11+Event_fcst_qty_11)+sum(Reg_fcst_qty_12+Event_fcst_qty_12)+sum(Reg_fcst_qty_13+Event_fcst_qty_13) wk13
from mrpdlib.CL113p cl113p,mrpdlib.it005p it005p,
(select item_number,vendor_number,distribution_center_nbr,max(CML_EFFECTIVE_DTE) EFF
from mrpdlib.cl113p cl113p
where inv_per_quarter=2
group by item_number,vendor_number,cl113p.distribution_center_nbr) Sub_eff_date
where cl113p.CML_EFFECTIVE_DTE=Sub_eff_date.EFF
and cl113p.item_number=Sub_eff_date.item_number
and cl113p.vendor_number=Sub_eff_date.vendor_number
and cl113p.distribution_center_nbr=Sub_eff_date.distribution_center_nbr
and cl113p.item_number=it005p.item_number
and cl113p.inv_per_quarter=2
and cl113p.CML_EFFECTIVE_DTE between (substr('1',1,1) concat substr(char(CURRENT_DATE - 7 days),3,2) concat substr(char(CURRENT_DATE - 7 days),6,2) concat substr(char(CURRENT_DATE - 7 days),9,2))
and (substr('1',1,1) concat substr(char(CURRENT_DATE),3,2) concat substr(char(CURRENT_DATE),6,2) concat substr(char(CURRENT_DATE),9,2))
and cl113p.item_number in(
'113212',
'274968',
'301526',
'329284',
'167644',
'487400',
'487231',
'487399',
'415518',
'494486',
'196700',
'493468',
'491779',
'491780',
'421845',
'477463',
'638389',
'392129',
'390644',
'390642',
'951994',
'392355',
'196673',
'396568',
'300007',
'493789',
'633944',
'493389',
'179941',
'397387',
'481122',
'513220',
'491036',
'491888',
'635908',
'486065',
'557298',
'914508',
'914467',
'638554',
'632877',
'363624',
'456229',
'489458',
'629831',
'629834',
'630561',
'390256',
'633926',
'398944',
'221700',
'473867',
'502887',
'932196',
'509201',
'561668',
'330122',
'453833',
'931980',
'275196',
'155524',
'453836',
'931980',
'901017',
'901018',
'256185',
'630143',
'680778',
'243878',
'650970',
'717001',
'152213',
'915490',
'350559',
'350558',
'350576',
'908434',
'908435',
'907771',
'434774',
'285375',
'285373',
'285374',
'472248',
'917299',
'917237',
'917307',
'387572',
'387571',
'610847',
'609517',
'382710',
'609514',
'609956',
'631780',
'499499',
'632186',
'632185',
'632196',
'820562',
'820563',
'820564',
'820591',
'822075',
'821344',
'600880',
'600878',
'771880',
'913700',
'825886',
'853722',
'902030',
'647583',
'615649',
'587140',
'811884',
'430293',
'367348',
'357849',
'913614',
'901492',
'326804',
'541450',
'381522',
'656123',
'656319',
'850373',
'507236',
'874275',
'874274',
'874219',
'587730',
'808437',
'808441',
'808442',
'808445',
'466353',
'466348',
'466315',
'466312',
'170306',
'170308',
'900865',
'900864',
'175292'
)
group by cl113p.item_number,cl113p.distribution_center_nbr) CL113b
on substr(dos.planogram_link_nbr,6,6)=CL113b.item_number
and dos.distribution_center_nbr=cl113b.distribution_center_nbr
left outer join mrpdlib.cl112p cl112p
on cl112p.item_number=substr(dos.planogram_link_nbr,6,6)
and cl112p.distribution_center_nbr=dos.distribution_center_nbr
left outer join mrpdlib.ve001p ve001p
on cl112p.vendor_number=ve001p.vendor_number
where sd5p.bdm>0
and it830p.item_lifecycle_status in ('L','N')
and dos.distribution_center_nbr in('88004','88024','88047')
)
;
disconnect from db2;
quit;
libname NZ Netezza server =Prdnzdba database=PRD_MERCHANT_PORTAL_DB user=smaccarthy password="xxxxxxx";
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_FCT_SSIS_DAY_CHN_IOH AS
SELECT t2.PLN_NBR,
/* BDM */
(SUM(t1.BDM_STR_COUNT)) FORMAT=11. LABEL="BDM" AS BDM,
/* IOH */
(SUM(t1.STR_WITH_IOH)) FORMAT=11. LABEL="IOH" AS IOH,
t3.DAY_DATE
FROM NZ.FCT_SSIS_DAY_CHN_IOH t1, NZ.DIM_PROD_PLN t2, NZ.DIM_PERIOD t3
WHERE (t1.PROD_ID = t2.PROD_ID AND t1.PER_ID = t3.PER_ID) AND t3.DAY_DATE = today()-1
GROUP BY t2.PLN_NBR,
t3.DAY_DATE;
QUIT;
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_FDOS_REPORT AS
SELECT DISTINCT t1.DISTRIBUTION_CENTER_NBR,
t1.FIVEWK,
/* SSIS */
(t2.IOH/t2.BDM) FORMAT=NLPCT6.5 LABEL="SSIS" AS SSIS,
t1.MERCH_OPSTUDY_DESC,
t1.VENDOR_NUMBER,
t1.SAFETY_STOCK_QTY,
t1.VENDOR_NAME1,
t1.PLN_NBR,
t1.ITEM_DESCRIPTION,
t1.BDM,
t1.ITEM_LIFECYCLE_STATUS,
t1.REG_IOH,
t1.DISTR_IOH,
t1.NET_OPEN_ORDER,
t1.'IOH DOS'n,
t1.'OO DOS'n,
t1.'IOH OO DOS'n,
t1.FDOS_IOH,
t1.FDOS_IOH_OO_NEW,
t1.ITEM_NUMBER,
t1.WK1,
t1.WK2,
t1.WK3,
t1.WK4,
t1.WK5,
t1.WK6,
t1.WK7,
t1.WK8,
t1.WK9,
t1.WK10,
t1.WK11,
t1.WK12,
t1.WK13
FROM WORK.FDOS_REPORT t1
LEFT JOIN WORK.QUERY_FOR_FCT_SSIS_DAY_CHN_IOH t2 ON (t1.PLN_NBR = t2.PLN_NBR);
QUIT;
libname Lindsay '/sas_env/empl/scm/SCM_SAS_PROD/DEV';
proc export data= work.FDOS_Report dbms= xlsx outfile='/sas_env/empl/scm/SCM_SAS_PROD/DEV/FDOS_Report.xlsx' replace; sheet="Sheet_1"; run;
filename outbox email 'lindsay.bridges@walgreens.com';
data _null_;
file outbox
to=('lindsay.bridges@walgreens.com')
from='lindsay.bridges@walgreens.comm'
cc=('lindsay.bridges@walgreens.com')
subject='SAS Notification: FDOS Report'
attach=("/sas_env/empl/scm/SCM_SAS_PROD/DEV/FDOS_Report.xlsx" CT="application/excel")
replyto='lindsay.bridges@walgreens.com'
IMPORTANCE= 'HIGH'
sender='lindsay.bridges@walgreens.com'
;
put 'All,';
Put ' ';
put 'Attached is the FDOS Report for Jason Cho.'; Put ' '; put 'Please be sure to click on enable to view the data.'; Put ' '; put 'This report is scheduled to run on Fridays at 7:00am.'; Put ' '; put 'This is a fully automated report created via SAS. Please let us know if you have questions.';
run;
