*************************************
*Created: July 2003                 *
*Author: J.McAllister               *
*				    *
*Objective: Predict items most      *
*likely to cause estimates to fall  *
*out of the +/- 10% range.          *
*                                   *
*Required:  Data view to compare    *
* against.  ODBC access to P/L data.*
*                                   *
*************************************;

***NOTE:  MUST RUN LAS INIT FIRST!!****;



**:Libnames to the p/l db and also to view with recent letting data***
**Current view in use contains 3 years (rolling) worth of data******;

libname db oracle user=xxx schema=xxx pw=xxx path='xxx';
libname let 'f:\sadss5\view\data3e';

**subset by letting and get the item info**;
data lprop;
 set db.letprop;
  where letting='03-09-24';
  if letstat =' ';
   keep lcontid letting;
    rename lcontid=contid;
run;

proc sort data=lprop;
 by contid;
run;

data pitem;
 set db.propitem;
  keep contid iplineno lineflag piextamt prpitem price qty;
run;

proc sort data=pitem;
 by contid;
run;

data eitems;
 merge lprop(in=a)
       pitem(in=b);
	    by contid;
		 if a and b;
run;

***get the estimate total to use in calculating percentage***;
data esttot;
 set eitems;
  where lineflag='P';
   rename piextamt=esttot;
run;

data esttot;
 set esttot;
  keep contid esttot;
run;

***eliminate section totals and estimate totals to calculate cumulative pct**;
***merge estimate total back in as new variable to use in calculation***;
data eitems;
 set eitems; 
  where lineflag ='L';
   rename prpitem=item;   
run;

data eitems;
 merge eitems
       esttot;
	    by contid;
run;

proc sort data=eitems;
 by contid descending piextamt;
run;

data pct;
 set eitems;
  by contid descending piextamt;
   retain pct 0;
    if first.contid then
	 do;
	  pct=(piextamt/esttot)*100;
	 end;
	  else pct=pct+(piextamt/esttot*100);
run;

**eliminate any items that are greater than 80% cumulative pct**
**(because 80% of total cost is contained in 20% of the items)**;
data aitems;
 set pct;
  where pct<=80;
   keep contid piextamt item qty price iplineno;
run;

**eliminate misc itemtyp and lump sum items.  Restrict to English***;
data list;
 set db.itemlist;
  where ispecyr='02' and iunits ne 'LS' and itemclss ne "MISC" and itemtype ne "MISC"        
        and substr(item,4,1)='E'and item ne: '530' and item ne '614E15000';
run;

proc sort data=list;
 by item;
run;

proc sort data=aitems;
 by item;
run;

data aitems;
 merge aitems(in=a)
       list(in=b);
	    by item;
		 if a and b;
		   keep contid piextamt item qty price iplineno;
run;

***get district and worktype to consider in predictions***;
proc sort data=aitems;
 by contid;
run;

data aitems;
 merge aitems(in=a)
       db.proposal(in=b);
	    by contid;
		 if a and b;
		  keep contid piextamt item qty price iplineno cfacssup propwork;
run;

data aitems;
 set aitems;
  rename cfacssup=cndistr;
  rename propwork=cnprpwrk;
run;

***get the top 4 bidders for each proposal in the view***;
data bids; 
 set let.dbidders(read=password);
run;

proc sort data=bids;
 by contid bdtotal;
run;

data bids;     
  set bids;
   by contid bdtotal;
   retain rank 0;
    if first.contid then
     do;
      rank = 1;
    end;
      else rank = rank+1;
run;

data bids;
 set bids; 
  where rank<5;
run;

***get the district and worktype to merge with the bids***;
***get recent bid tabs for the subset of analysis items***;
***these are contained in view in DSS,defined in libname***
***Limit to the top 4 bidders, as defined in previous steps***;
proc sort data=bids;
 by contid vendor;
run;

data bids;
 merge bids(in=a)
       let.dproposl(in=b read=password);
	    by contid;
		 keep contid vendor bdtotal rank cndistr cnprpwrk;
run;

proc sort data=bids;
 by contid vendor;
run;

data tabs;
 merge let.dbidtabs(read=password in=a)
       bids(in=b);
	    by contid vendor;
		 if a and b;
run;

***merge the bid data with the set of estimate analysis items in aitem***;
proc sort data=tabs;
 by item cndistr cnprpwrk;
run;

proc sort data=aitems;
 by item cndistr cnprpwrk;
run;

data tabs; 
 merge aitems(in=a)
       tabs(in=b);
	    by item cndistr cnprpwrk;
	 if a and b;
run;

**eliminate unnecessary variables**;
data tabs;
 set tabs;
 keep item btoqty btuprice cndistr cnprpwrk;
run;

**eliminate the 5 highest quantities**;
proc sort data=tabs;
 by item descending btoqty;
run;

data tabs;
 set tabs; 
  by item descending btoqty;
   retain high 0;
    if first.item then 
	 do;
      high=1;
	end;
	 else;
	  high=high+1;
run;

data tabs;
 set tabs;
  where high>5;
run;

**eliminate the 5 lowest quantities**;
proc sort data=tabs;
 by item btoqty;
run;

data tabs;
 set tabs;
  by item btoqty;
   retain low 0;
    if first.item then
	 do;
	  low=1;
	end;
	 else;
	  low=low+1;
run;

data tabs;
 set tabs;
  where low>5;
run;

***define quantity ranges for each item in the tabs dataset by***
***district and worktypes***;
proc sort data=tabs;
 by item cndistr cnprpwrk;
run;

proc summary data=tabs;
 by item cndistr cnprpwrk;
  var btoqty;
output out=qty min=lowqty max=hiqty mean=avqty;
run;

data tabqty;
 merge tabs
       qty;
	    by item cndistr cnprpwrk;
run; 

data qtyrng;
 set tabqty;
  by item cndistr cnprpwrk;
  drop _type_ _freq_;
 if btoqty <= lowqty then qtyrng=1;
 if lowqty < btoqty <= (avqty+lowqty)/2 then qtyrng=2;
 if (avqty+lowqty)/2 < btoqty <= avqty then qtyrng=3;
 if avqty < btoqty <= (avqty+hiqty)/2 then qtyrng=4;
 if (avqty+hiqty)/2 < btoqty <= hiqty then qtyrng=5;
run;

***assign the same quantity ranges to the data in the estimates***;
data qtyest;
 set qtyrng;
  drop qtyrng btuprice btoqty;
run;

proc sort data=qtyest nodupkey;
 by item cndistr cnprpwrk;
run;

data aqty;
 merge qtyest(in=a)
       aitems(in=b);
	    by item cndistr cnprpwrk;
		 if a and b;
run;

data aqty;
 set aqty;
  by item cndistr cnprpwrk;
 if qty <= lowqty then qtyrng=1;
 if lowqty < qty <= (avqty+lowqty)/2 then qtyrng=2;
 if (avqty+lowqty)/2 < qty <= avqty then qtyrng=3;
 if avqty < qty <= (avqty+hiqty)/2 then qtyrng=4;
 if (avqty+hiqty)/2 < qty <= hiqty then qtyrng=5;
 if qty > hiqty then qtyrng=5;
run;

***drop unneeded variables from aqty***;
data aqty;
 set aqty;
  drop lowqty avqty hiqty;
run;

***get an average unit price for each item/quantity***
***range in the bid data. Eliminate unneeded variables***;
proc sort data=qtyrng;
 by item qtyrng;
run;

proc summary data=qtyrng;
 by item qtyrng;
  var btuprice;
output out=bidprc mean=avprc;
run;

data bidprc;
 set bidprc;
  drop _type_ _freq_;
run;

***merge with estimate items.  Project the bid price by multiplying*******
***the avprc for that item quantity range/dist/wtype by the actual qty****
***from the estimate.  Find items where the estimate differs from*********
***the projected bid price by more than 1% of the total estimate**********
***Make note of items having no comparison available. Merge in************
***the estimate total for the calculation***;
proc sort data=aqty;
 by item qtyrng;
run;

data nocomp comp;
 merge aqty(in=a)
       bidprc(in=b);
	    by item qtyrng;
		 if a and not b then output nocomp;
		 if a and b then output comp;
run;

data comp;
 set comp;
  bidguess=avprc*qty;
run;

proc sort data=comp;
 by contid;
run;

data compa;
 merge comp(in=a)
       esttot(in=b);
	    by contid;
		 if a and b;
run;

data pctdif;
 set compa;
  where abs(piextamt-bidguess) > esttot*.02;
   pctdiff = (piextamt-bidguess)/esttot*100;
run;

***get some descriptive proposal information for the report***;
proc sort data=pctdif;
 by contid;
run;

data props;
 merge pctdif(in=a)
       db.proposal;
	    by contid;
		 if a;
		  keep pralt1 contid cdescr cntlpcn cnprpwrk iplineno item 
               qty price avprc cndistr pctdiff;
run;

proc sort data=props;
 by cntlpcn;
run;

data props;
 set props;
  rename cntlpcn=pcn;
run;

data props_all;
 merge props(in=a)
       db.project;
	    by pcn;
		 if a;
		  keep pralt1 contid cdescr pcn cnprpwrk iplineno item pestby
		       qty price avprc pctdiff;
run; 

data props_all;
 set props_all;
  Reliability='A';
run;

***add in descriptions and units for items***;
proc sort data=props_all;
 by item;
run;
data props_all;
 merge props_all(in=a)
       list(in=b);
	    by item;
		 if a and b;
		  keep pralt1 contid cdescr pestby cnprpwrk iplineno
		       item  idescr iunits qty price avprc pctdiff reliability;
run;

***DEBUG PRINT***;
***proc print data=props_all noobs;
***run;
***title1 'LIP Prediction - district,work type, quantity range'***;
***run;
***END DEBUG PRINT***;

****Phase II - Prediction with one less parameter********
****Start with nocomp, eliminate worktype as parameter***
****Repeat steps from phase I considering dist & qty.****;
data tabs;
 merge let.dbidtabs(read=password in=a)
       bids(in=b);
	    by contid vendor;
		 if a and b;
run;

***merge the bid data with the set of estimate analysis items in aitem***;
proc sort data=tabs;
 by item cndistr;
run;
proc sort data=aitems;
 by item cndistr;
run;
data tabs; 
 merge aitems(in=a)
       tabs(in=b);
	    by item cndistr;
	 if a and b;
run;

***define the quantity ranges***;
proc summary data=tabs;
 by item cndistr;
  var btoqty;
output out=qty min=lowqty max=hiqty mean=avqty;
run;
data tabqty;
 merge tabs
       qty;
	    by item cndistr;
run; 

data qtyrng;
 set tabqty;
  by item cndistr;
  drop _type_ _freq_;
 if btoqty <= lowqty then qtyrng=1;
 if lowqty < btoqty <= (avqty+lowqty)/2 then qtyrng=2;
 if (avqty+lowqty)/2 < btoqty <= avqty then qtyrng=3;
 if avqty < btoqty <= (avqty+hiqty)/2 then qtyrng=4;
 if (avqty+hiqty)/2 < btoqty <= hiqty then qtyrng=5;
run;

***assign the same quantity ranges to the data in the estimates***;
data qtyest;
 set qtyrng;
  drop qtyrng btuprice btoqty;
run;

proc sort data=qtyest nodupkey;
 by item cndistr;
run;

data aqty;
 merge qtyest(in=a)
       aitems(in=b);
	    by item cndistr;
		 if a and b;
run;

data aqty;
 set aqty;
  by item cndistr;
 if qty <= lowqty then qtyrng=1;
 if lowqty < qty <= (avqty+lowqty)/2 then qtyrng=2;
 if (avqty+lowqty)/2 < qty <= avqty then qtyrng=3;
 if avqty < qty <= (avqty+hiqty)/2 then qtyrng=4;
 if (avqty+hiqty)/2 < qty <= hiqty then qtyrng=5;
 if qty > hiqty then qtyrng=5;
run;

***drop unneeded variables from aqty***;
data aqty;
 set aqty;
  drop lowqty avqty hiqty;
run;

***get an average unit price for each item/quantity***
***range in the bid data. Eliminate unneeded variables***;
proc sort data=qtyrng;
 by item qtyrng;
run;

proc summary data=qtyrng;
 by item qtyrng;
  var btuprice;
output out=bidprc mean=avprc;
run;

data bidprc;
 set bidprc;
  drop _type_ _freq_;
run;

***merge with estimate items.  Project the bid price by multiplying***
***the avprc for that item quantity range/dist/wt by the actual qty******
***from the estimate.  Find items where the estimate differs from*****
***the projected bid price by more than 1% of the total estimate*****
***Make note of items having no comparison available. Merge in*******
***the estimate total for the calculation***;
proc sort data=aqty;
 by item qtyrng;
run;
data nocomp comp;
 merge aqty(in=a)
       bidprc(in=b);
	    by item qtyrng;
		 if a and not b then output nocomp;
		 if a and b then output comp;
run;

data comp;
 set comp;
  bidguess=avprc*qty;
run;

proc sort data=comp;
 by contid;
run;
data compb;
 merge comp(in=a)
       esttot(in=b);
	    by contid;
		 if a and b;
run;

data pctdif;
 set compb;
  where abs(piextamt-bidguess) > esttot*.02;
   pctdiff = (piextamt-bidguess)/esttot*100;
run;

***get some descriptive proposal information for the report***;
proc sort data=pctdif;
 by contid;
run;
data 
props;
 merge pctdif(in=a)
       db.proposal;
	    by contid;
		 if a;
		  keep pralt1 contid cdescr cntlpcn cnprpwrk iplineno item 
               qty price avprc cndistr pctdiff;
run;

proc sort data=props;
 by cntlpcn;
run;

data props;
 set props;
  rename cntlpcn=pcn;
run;

data props_dq;
 merge props(in=a)
       db.project;
	    by pcn;
		 if a;
		  keep pralt1 contid cdescr pcn cnprpwrk iplineno item pestby
		       qty price avprc pctdiff;
run; 

***add in descriptions and units for items***;
proc sort data=props_dq;
 by item;
run;

data props_dq;
 set props_dq;
  Reliability='B';
run;

data props_dq;
 merge props_dq(in=a)
       list(in=b);
	    by item;
		 if a and b;
		  keep pralt1 contid cdescr pestby cnprpwrk iplineno
		       item  idescr iunits qty price avprc pctdiff reliability;
run;
***DEBUG PRINT***;
***proc print data=props_dq noobs;
***run;
***title2 'LIP Prediction - quantity level and district';
***run;
***END DEBUG PRINT***;

****Phase III - Prediction with one less parameter********
****Start with next dataset, eliminate worktype and district****
****as parameters. Repeat steps from phase I considering qty only.*****;
data tabs;
 merge let.dbidtabs(read=password in=a)
       bids(in=b);
	    by contid vendor;
		 if a and b;
run;

***merge the bid data with the set of estimate analysis items in aitem***;
proc sort data=tabs;
 by item;
run;

proc sort data=aitems;
 by item;
run;

data tabs; 
 merge aitems(in=a)
       tabs(in=b);
	    by item;
	 if a and b;
run;

**define the quantity ranges**;
proc summary data=tabs;
 by item;
  var btoqty;
output out=qty min=lowqty max=hiqty mean=avqty;
run;

data tabqty;
 merge tabs
       qty;
	    by item;
run; 

data qtyrng;
 set tabqty;
  by item;
  drop _type_ _freq_;
 if btoqty <= lowqty then qtyrng=1;
 if lowqty < btoqty <= (avqty+lowqty)/2 then qtyrng=2;
 if (avqty+lowqty)/2 < btoqty <= avqty then qtyrng=3;
 if avqty < btoqty <= (avqty+hiqty)/2 then qtyrng=4;
 if (avqty+hiqty)/2 < btoqty <= hiqty then qtyrng=5;
run;

***assign the same quantity ranges to the data in the estimates***;
data qtyest;
 set qtyrng;
  drop qtyrng btuprice btoqty;
run;

proc sort data=qtyest nodupkey;
 by item;
run;

data aqty;
 merge qtyest(in=a)
       aitems(in=b);
	    by item;
		 if a and b;
run;

data aqty;
 set aqty;
  by item;
if qty <= lowqty then qtyrng=1;
 if lowqty < qty <= (avqty+lowqty)/2 then qtyrng=2;
 if (avqty+lowqty)/2 < qty <= avqty then qtyrng=3;
 if avqty < qty <= (avqty+hiqty)/2 then qtyrng=4;
 if (avqty+hiqty)/2 < qty <= hiqty then qtyrng=5;
 if qty > hiqty then qtyrng=5;
run;

***drop unneeded variables from aqty***;
data aqty;
 set aqty;
  drop lowqty avqty hiqty;
run;

***get an average unit price for each item/quantity***
***range in the bid data. Eliminate unneeded variables***;
proc sort data=qtyrng;
 by item qtyrng;
run;

proc summary data=qtyrng;
 by item qtyrng;
  var btuprice;
output out=bidprc mean=avprc;
run;

data bidprc;
 set bidprc;
  drop _type_ _freq_;
run;

***merge with estimate items.  Project the bid price by multiplying***
***the avprc for that item quantity range/dist/wt by the actual qty******
***from the estimate.  Find items where the estimate differs from*****
***the projected bid price by more than 1% of the total estimate*****
***Make note of items having no comparison available. Merge in*******
***the estimate total for the calculation***;
proc sort data=aqty;
 by item qtyrng;
run;

data nocomp comp;
 merge aqty(in=a)
       bidprc(in=b);
	    by item qtyrng;
		 if a and not b then output nocomp;
		 if a and b then output comp;
run;

data comp;
 set comp;
  bidguess=avprc*qty;
run;

proc sort data=comp;
 by contid;
run;

data compc;
 merge comp(in=a)
       esttot(in=b);
	    by contid;
		 if a and b;
run;

data pctdif;
 set compc;
  where abs(piextamt-bidguess) > esttot*.02;
   pctdiff = (piextamt-bidguess)/esttot*100;
run;

***get some descriptive proposal information for the report***;
proc sort data=pctdif;
 by contid;
run;
data props;
 merge pctdif(in=a)
       db.proposal;
	    by contid;
		 if a;
		  keep pralt1 contid cdescr cntlpcn cnprpwrk iplineno item 
               qty price avprc cndistr pctdiff;
run;

proc sort data=props;
 by cntlpcn;
run;

data props;
 set props;
  rename cntlpcn=pcn;
run;

data props_q;
 merge props(in=a)
       db.project;
	    by pcn;
		 if a;
		  keep pralt1 contid cdescr pcn cnprpwrk iplineno item pestby
		       qty price avprc pctdiff;
run; 

***add in descriptions and units for items***;
proc sort data=props_q;
 by item;
run;

data props_q;
 set props_q;
  Reliability='C';
run;

data props_q;
 merge props_q(in=a)
       list(in=b);
	    by item;
		 if a and b;
		  keep pralt1 contid cdescr pestby cnprpwrk iplineno
		       item  idescr iunits qty price avprc pctdiff reliability;
run;

***DEBUG PRINT***;
***proc print data=props_q noobs;
***title1 'LIP Prediction - Quantity level only';
***run;
***END DEBUG PRINT***;

***Compile results of all 3 phases***;
proc sort data=props_all;
 by contid pralt1 iplineno reliability;
run;

proc sort data=props_dq;
 by contid pralt1 iplineno reliability;
run;

proc sort data=props_q;
 by contid pralt1 iplineno reliability;
run;

data props1;
 merge props_all(in=a)
       props_dq(in=b);
	    by contid pralt1 iplineno;
		 if b and not a;
run;

data props2;
 merge props1
       props_all;
	    by contid pralt1 iplineno;
run;

data props3;
 merge props_q(in=a)
       props2(in=b);
	    by contid pralt1 iplineno;
		 if a and not b;
run;

data props_final;
 merge props2
       props3;
	    by contid pralt1 iplineno reliability;
run;