Re: Remove Duplicate rows

lbridges225 · Posted 07-12-2016 12:24 PM

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;

Reeza · Posted 07-12-2016 12:38 PM

I would figure out why I have duplicates and modify that query.

I'm not going to read that much code either.... You should simplify your question.

http://stackoverflow.com/help/mcve

lbridges225 · Posted 07-12-2016 12:47 PM

Sorry this was my first post! Here's a snapshot of the data. Distribution Center Number and related fields are repeating....I want to clean this up...thanks everyone!

DISTRIBUTION_CENTER_NBR	FIVEWK	SSIS	MERCH_OPSTUDY_DESC	VENDOR_NUMBER	SAFETY_STOCK_QTY	VENDOR_NAME1	PLN_NBR
88047	1441	100.00%	WATERS	059656	13407	PACTECH INTERNATIONAL	40000113212
88047	1441	100.00%	WATERS	059656	12090	PACTECH INTERNATIONAL	40000113212
88047	1441	100.00%	WATERS	059656	11847	PACTECH INTERNATIONAL	40000113212
88004	219	99.69%	BATH & SOAP	089425	1728	VI-JON SOAP/COS MISSOURI	40000152213
88004	219	99.69%	BATH & SOAP	089425	3672	VI-JON SOAP/COS MISSOURI	40000152213
88004	787	99.06%	FIRST AID	049276	6294	MEDLINE INDUSTRIES	40000155524
88004	787	99.06%	FIRST AID	049276	4794	MEDLINE INDUSTRIES	40000155524

Reeza · Posted 07-12-2016 12:54 PM

Those aren't exact duplicates. The safety stock variable is unique per row.

Reeza · Posted 07-12-2016 12:54 PM

Those aren't exact duplicates. The safety stock variable is unique per row.

RW9 · Posted 07-12-2016 12:42 PM

Hi,

Sorry, not to sound off here, but that is a vast chunk of code, all in mixed case, with uneven or no indetation, and no test data. At a quick glance a fair bit of that code is redundant, but I am not here to re-write. Nodupkey can remove duplicates, the documentation has plenty of explanation on it:

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000146878.htm

As a tip, the vast majority of that code is caused by using a transposed dataset (i.e. having week number as variables). Not really good practice, in SQL as well. Fix your data, and then use procedures to do sums and such like.

Remove Duplicate rows