Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- Home
- /
- About lbridges225
07-12-2017
lbridges225
Calcite | Level 5
Member since
06-29-2016
- 8 Posts
- 1 Likes Given
- 0 Solutions
- 0 Likes Received
-
Latest posts by lbridges225
Subject Views Posted 4459 07-11-2017 04:29 PM 4523 06-15-2017 02:24 PM 4529 06-15-2017 01:36 PM 4573 06-14-2017 05:03 PM 27139 04-12-2017 03:42 PM 27150 04-12-2017 03:18 PM 1203 07-12-2016 12:47 PM 1215 07-12-2016 12:24 PM -
Activity Feed for lbridges225
- Posted Re: How to create a dynamic date range on SAS Programming. 07-11-2017 04:29 PM
- Posted Re: How to create a dynamic date range on SAS Programming. 06-15-2017 02:24 PM
- Posted Re: How to create a dynamic date range on SAS Programming. 06-15-2017 01:36 PM
- Liked Re: How to create a dynamic date range for ChrisNZ. 06-15-2017 01:35 PM
- Posted How to create a dynamic date range on SAS Programming. 06-14-2017 05:03 PM
- Posted Re: Convert Date to Character on SAS Programming. 04-12-2017 03:42 PM
- Posted Convert Date to Character on SAS Programming. 04-12-2017 03:18 PM
- Posted Re: Remove Duplicate rows on SAS Procedures. 07-12-2016 12:47 PM
- Posted Remove Duplicate rows on SAS Procedures. 07-12-2016 12:24 PM
-
Posts I Liked
Subject Likes Author Latest Post 1
07-11-2017
04:29 PM
So for my project I had a fiscal calendar.csv uploaded into SAS since our fiscal months here are different from others - which is weird I know. I think I need to write some data steps to reference this csv and pull the fiscal month start and end dates dynamically. So far I have a proc sql statement and a data statement....not sure how to proceed... DATA CALENDAR; INFILE "//sas_env/xxxcl/scm/Fxqhek/fiscal_calendar_month_start_end.csv" DELIMITER = ',' MISSOVER DSD LRECL=32767 FIRSTOBS=2 ; INFORMAT CALENDAR_DATE MMDDYY10.; INFORMAT MONTH_START MMDDYY10.; INFORMAT MONTH_END MMDDYY10.; FORMAT CALENDAR_DATE MMDDYY10.; FORMAT MONTH_START MMDDYY10.; FORMAT MONTH_END MMDDYY10.; INPUT CALENDAR_DATE MONTH_START MONTH_END ; IF CALENDAR_DATE = TODAY(); RUN; Is this Proc sql statement the way to go? PROC SQL OUTOBS=1; CREATE TABLE QUERY_DATES_2017 AS SELECT * FROM CALENDAR WHERE FISCAL_MONTH = &FISCAL_MONTH AND FISCAL_YEAR = &FISCAL_YEAR_2017; QUIT; or should I be using this data null statement DATA _NULL_; SET CALENDAR_TODAY; CALL SYMPUT ("FISCAL_MONTH", FISCAL_MONTH_2017); RUN; Thanks.
... View more
06-15-2017
02:24 PM
@Patrick The %let statements worked!! Thank you! How would I do the same thing for this statement, sp that it pulls the next series of dates without manual manipulation? So as an example, I would need to run this in July for 5/28 - 6/30, and again in August... WHERE PER.DAY_DATE BETWEEN to_date('04/30/2017','mm/dd/yyyy') and to_date('05/27/2017','mm/dd/yyyy') Thank you!
... View more
06-15-2017
01:36 PM
Sorry I meant to mention that yes this is a pass-thru to an oracle database
... View more
06-14-2017
05:03 PM
I need to run a query each month that uses October 1, 2016 up to the most recent month end date. So in this case I ran the query for 10/1/2016 - 5/31/2017. Next run will be 10/1/2016 to 6/30/2017. I'm trying to code this in SAS and this is as far as I've gotten..and I'm 100% new so... %let mnth = 5; %let yr = 2016; %let fy= %substr(&year,3,2)%sysfunc(putn(%eval(%substr(&year,3,2)+1), z2.)); My goal is to no longer have to modify the dates below manually. WHERE PER.DAY_DATE BETWEEN to_date('10/01/2016','mm/dd/yyyy') and to_date('05/31/2017','mm/dd/yyyy') Thanks everyone
... View more
04-12-2017
03:42 PM
I want to convert YR_2015, YR_2016 and MONTH_START_DATE from date to character. I am trying to use a PUT but not having any luck. Someone said I need a new PROC SQL statement...but not sure how to begin... DATA CALENDAR2; SET CALENDAR; FORMAT QUERY_QTR $10.; QUERY_QTR = "FY"||COMPRESS(SUBSTR(FYQUARTER,3,2))||' '||"Q"||COMPRESS(SUBSTR(FYQUARTER,6,1)); FORMAT YR_2016 MMDDYY10.; PUT(YR_2016,DATE9.)AS YR_2016; YR_2016 = MONTH_START_DATE - 364; PUT (MONTH_START_DATE,DATE9.) AS MONTH_START_DATE; FORMAT YR_2015 MMDDYY10.; PUT (YR_2015, DATE9.) AS YR_2015; YR_2015 = YR_2016 - 364; RUN;
... View more
04-12-2017
03:18 PM
PROC SQL SELECT PUT(YR_2016,DATE9.)AS YR_2016; PUT (YR_2015, DATE9.) AS YR_2015; PUT (MONTH_START_DATE,DATE9.) AS MONTH_START_DATE; DATA CALENDAR; INFILE "//sas_env/empl/scm/Mahek/hw_query_calendar.csv" DELIMITER = ',' MISSOVER DSD LRECL=32767 FIRSTOBS=2 ; INFORMAT CALENDAR_DATE MMDDYY10.; INFORMAT FISCAL_YEAR 6.; INFORMAT FISCAL_MONTH 6.; INFORMAT FYQUARTER $6.; INFORMAT MONTH_START_DATE MMDDYY10.; FORMAT CALENDAR_DATE MMDDYY10.; FORMAT FISCAL_YEAR 6.; FORMAT FISCAL_MONTH 6.; FORMAT FYQUARTER $6.; FORMAT MONTH_START_DATE MMDDYY10.; INPUT CALENDAR_DATE FISCAL_YEAR FISCAL_MONTH FYQUARTER $ MONTH_START_DATE ; IF CALENDAR_DATE = TODAY(); RUN; DATA CALENDAR2; SET CALENDAR; FORMAT QUERY_QTR $10.; QUERY_QTR = "FY"||COMPRESS(SUBSTR(FYQUARTER,3,2))||' '||"Q"||COMPRESS(SUBSTR(FYQUARTER,6,1)); FORMAT YR_2016 MMDDYY10.; PUT(YR_2016,DATE9.)AS YR_2016; YR_2016 = MONTH_START_DATE - 364; PUT (MONTH_START_DATE,DATE9.) AS MONTH_START_DATE; FORMAT YR_2015 MMDDYY10.; PUT (YR_2015, DATE9.) AS YR_2015; YR_2015 = YR_2016 - 364; RUN;
... View more
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
... View more
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;
... View more
