*************************************************************
* BAMS/DSS AdHoc Program AVERAGE: Average Unit Price Report	*
*										*
* Objective:  List selected data in the BAMS/DSS DPROPOSL,	*
*             DBIDTABS, DBIDDERS, VENDOR & ITEMLIST data	*
*		  sets.							*
*										*
* This complex program generates a Customizable reports	*
* of Average Unit Prices, Bidders List and Mobilization	*
* Summary.									*
*										*
* Input:  DPROPOSL,DBIDTABS DBIDDERS, VENDOR & ITEMLIST	*
*			(selected fields)					*
* mrm 10/02/00								*
*************************************************************;


*************************************************************
************ 	    SYSTEM PARAMETERS		*************
*************************************************************;


***** The OPTION statement changes the program parameters.  *****
***** In this case it forces the output to 80 columns.      *****
***** The default is 132 columns.                           *****;

* option linesize=80;                                      
option linesize=132;
option pagesize=60;                                      

options source source2 symbolgen;

libname library 'e:\sadss5\library';



*************************************************************
************ CREATE TEMP FILES > DATABASE VIEWS	*************
*************************************************************;

data TEMPPROP;
	set VIEW.DPROPOSL(read=&qviewpw);
	keep contid cnprjnum cndtlet cncall cnpcn ispecyr 
		cndistr cnprpwrk cnnumbid cnawdamt;
run;

data TEMPBTAB;
	set VIEW.DBIDTABS(read=&qviewpw);
	keep contid vendor item btuprice btoqty btextamt;
run;

data TEMPBDRS;
	set VIEW.DBIDDERS(read=&qviewpw);
	keep contid vendor bdtotal;
run;

data TEMPVEND;
   set VIEW.VENDOR(read=&qviewpw);
   keep vendor vnamel;
run;

data TEMPITEM;
	set VIEW.ITEMLIST(read=&qviewpw);
	keep item idescr ilsst1 iunits;
run;



*************************************************************
************	SORT TEMPORARY DATA FILES	*************
*************************************************************;

proc sort data=TEMPPROP;
	by contid;
run;

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

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



*************************************************************
************	SELECT LOW THREE BIDDERS	*************
*************************************************************;

data TEMP3LOW;
	set TEMPBDRS;
	by contid;
	avprice=' ';
	retain i;
	if first.contid then i=0;
	if i<3 then do;
	i+1;
	output;
	end;
run;

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



*************************************************************
************    CREATE TEMPORARY DATA FILES	*************
************  BIDDERS & MOBILIZATION SUMMARIES	*************
*************************************************************;

Data TEMPBSUM;
	Set TEMP3LOW;
	if (i<4);
run;

Data TEMPMOB;
	Set TEMP3LOW;
	if (i<4);
run;



*************************************************************
************	MERGE TEMPORARY DATA FILES	*************
*************************************************************;

data TEMP3LOW;
	merge TEMP3LOW(in=in3low)
		TEMPBTAB;
	by contid vendor;
	if in3low;
run;

data TEMP3LOW;
	merge TEMP3LOW(in=in3low)
		TEMPPROP;
	by contid;
	if in3low;
run;

proc sort data=TEMP3LOW;
	by item;
run;

data TEMP3LOW;
	merge TEMP3LOW(in=in3low)
		TEMPITEM;
	by item;
	if in3low;
run;



*************************************************************
************ SET-UP & SORT LOW BID & 3 LOW BIDS	*************
*************************************************************;

Data TEMP1LOW;
	Set TEMP3LOW;
	if (i=1);
run;

proc sort data=TEMP1LOW;
	by item;
run;

proc sort data=TEMP3LOW;
	by item cnpcn i;
run;



*************************************************************
************   SUMMARIZE LOW BID & 3 LOW BIDS	*************
*************************************************************;


PROC SUMMARY DATA= TEMP1LOW;
	BY ITEM;
	VAR BTEXTAMT BTOQTY;
	OUTPUT
		OUT=LOW1SUM
		SUM=EXTSUM1 QTYSUM1;
RUN;

DATA LOW1SUM;
	SET LOW1SUM;
	AVPRICE1=EXTSUM1/QTYSUM1;
RUN;


PROC SUMMARY DATA= TEMP3LOW;
	BY ITEM;
	VAR BTEXTAMT BTOQTY;
	OUTPUT
		OUT=LOW3SUM
		SUM=EXTSUM3 QTYSUM3;
RUN;

DATA LOW3SUM;
	SET LOW3SUM;
	AVPRICE3=EXTSUM3/QTYSUM3;
RUN;

DATA COMBLOW;
	MERGE LOW1SUM
		LOW3SUM;
	BY ITEM;
run;

DATA COMBALL;
	MERGE TEMP3LOW
		COMBLOW;
	BY ITEM;
run;



*************************************************************
************	 BIDDERS SUMMARY SECTION	*************
*************************************************************;

data TEMPBSUM;
   merge TEMPBSUM(in=inbsum)
         TEMPPROP;
   by contid;
   if inbsum;
run;

proc sort data=TEMPBSUM;
   by vendor;
run;

data TEMPVEND;
   set VIEW.VENDOR(read=&qviewpw);
   keep vendor vnamel;
run;

data TEMPBSUM;
   merge TEMPBSUM(in=inbsum)
         TEMPVEND;
   by vendor;
   if inbsum;
run;



*************************************************************
************    MOBILIZATION SUMMARY SECTION	*************
*************************************************************;

data TEMPBTAB;
   set TEMPBTAB;
   keep contid vendor item btuprice ;
   if item = "Z629-05A" then do ;
   output;
   end;
   if item = "Z629A1282" then do ;
   output;
   end;
run;

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

data TEMPMOB;
   merge TEMPMOB(in=inmob)
         TEMPBTAB;
   by contid vendor; 
   if inmob;
run;

data TEMPMOB;
   merge TEMPMOB(in=inmob)
         TEMPPROP;
   by contid;
   if inmob;
   if cndistr=14105 then dist=1;
   if cndistr=18111 then dist=1;
   if cndistr=18112 then dist=1;
   if cndistr=24105 then dist=2;
   if cndistr=28111 then dist=2;
   if cndistr=28121 then dist=2;
   if cndistr=34101 then dist=3;
   if cndistr=34105 then dist=3;
   if cndistr=38131 then dist=3;
   if cndistr=38132 then dist=3;
   if cndistr=38133 then dist=3;
   if cndistr=38134 then dist=3;
   if cndistr=48141 then dist=4;
   if cndistr=48142 then dist=4;
   if cndistr=48143 then dist=4;
   if cndistr=54105 then dist=5;
   if cndistr=58151 then dist=5;
   if cndistr=58152 then dist=5;
   if cndistr=68161 then dist=6;
   if cndistr=68162 then dist=6;
   if cndistr=93310 then dist=9;
run;

proc sort data=TEMPMOB;
   by dist bdtotal;
run;

data TEMPMOB;
   set TEMPMOB;
   mob = (btuprice*100)/(bdtotal-btuprice);
run;



*************************************************************
************   REPORTS SECTION - THREE REPORTS	*************
*************************************************************;

*************************************************************
************    AVERAGE UNIT PRICE REPORT - 1	*************
*************************************************************;

data _null_;
	set comball;
	by item;
	file print header=H line=room LL=remain n=59;

	if first.item and remain<8 then put _page_;
	if  last.item and remain<6 then put _page_;

	if first.item then do;
 	put / @1   '------------- ' 		
	   	@15  'Item No. = ' item $char13.
		@40  'Key = ' ilsst1 $char4.
		@60  'Units = ' iunits $char4.
		@80  'Desc = ' idescr $char30.
		@118 ' -------------'
	    /
	;
	end;
   
	put   @1  cndtlet	yymmdd6.
	   	@8  cnpcn $char5.
		@14 vendor $char4.
		@19 i 1.
		@30 cnprpwrk $char2.
		@34 cndistr $char1.
		@36 ilsst1
		@45 btoqty 12.3-r
		@61 btuprice dollar12.2
		@77 btextamt dollar12.2
		@106 cnprjnum $char26.
	;

	if last.item then
	put /	@5 'LOW BID AVERAGE       ' @45 QTYSUM1 12.3 @77 EXTSUM1 dollar12.2 @93 AVPRICE1 dollar12.2
          //@5 'THREE LOW BID AVERAGE ' @45 QTYSUM3 12.3 @77 EXTSUM3 dollar12.2 @93 AVPRICE3 dollar12.2
	    / @1 '==================================================================================================================================='
	;
	return;

H: 	put   @38 'IDAHO TRANSPORTATION DEPARTMENT - DIVISION OF HIGHWAYS'
          / @51 'BID AVERAGE UNIT PRICE REPORT'
          / @41 'AVERAGE UNIT PRICES FOR JAN 00 - JUL 00 PROJECTS'
	    //@14 'BIDDER' @27 'WI' @30 'WK' 
	    / @2 'OPEN' @8 'PROJ'  @14 'CO SEQ'     @27 'DT'              @30 '-T' 
		@33 'DI'  @36 'ITEM' @63 'UNIT PRICE' @79 '3 LOW BIDS' @106 'PROJECT NUMBER' 
	    / @2 'DATE' @8 'KEY'   @14 'DE NO.'     @21 'MILES'           @27 'H'  
            @30 'YP'   @33 'ST'   @36 'KEY'        @49 'QUANTITY'   @66 'BID'             @79 'BID AMOUNT' 
            @91 'AVG.UNIT PRICE'
	    / @1  '====== ===== ==== = ===== == == == ==== ================ =============== =============== =============== ==========================='
	     	;
	If first.item then do;
		return;
	end;
	Else do;
	Put / @ 1  '-(Continued)- ' 		
	    	@15  'Item No. = ' item $char13.
	 	@40  'Key = ' ilsst1 $char4.
	 	@60  'Units = ' iunits $char4.
	 		@80  'Desc = ' idescr $char30.
	 	@118 ' -(Continued)-'
		/;
	end;
	return;
run;



*************************************************************
************     BIDDERS SUMMARY REPORT - 2	*************
*************************************************************;

proc print data =TEMPBSUM split='/' NOOBS;
*   by vendor;
   var vendor vnamel cnprjnum bdtotal cndtlet cnpcn i cnnumbid cndistr ;
   format vendor $char5.;
   format vnamel $char40.;
   format cnprjnum $char30.;
   format bdtotal dollar15.2;
   format cndtlet yymmdd6.;
   format cnpcn $char4.;
   format i 1.;
   format cnnumbid 2.;   
   format cndistr $char1.;
   label vendor    = BDR/CODE/'------'
	   vnamel    = /BIDDER NAME/'-----------------'
	   cnprjnum  = PROJECT/NUMBER/'----------'
	   bdtotal   = /TOTAL BID/'--------------'
	   cndtlet   =  OPEN / DATE /'------'
         cnpcn     = /KEY/'----'
         i         = BDR/SEQ/'---'
         cnnumbid  = NO./BID/'---'
         cndistr   = DI/ST/'----'
         ;

*** The TITLE statements allow the user to label the first  ***
*** few lines of the output.                                ***;

title1 'IDAHO TRANSPORTATION DEPARTMENT-CONTRACT ADMINISTRATION SYSTEM';
title2 'LIST OF BIDDERS INCLUDED ON AVERAGE UNIT PRICE REPORT';
title3 '1/00 - 7/00 PROJECTS SORTED ON BIDDER CODE';
title4;
run;



*************************************************************
************   MOBILIZATION SUMMARY REPORT - 3	*************
*************************************************************;

proc print data =TEMPMOB split='/' NOOBS;
	var cndtlet cnpcn vendor i cnprpwrk dist bdtotal btuprice mob cnprjnum ;
	format cndtlet yymmdd6.;
	format cnpcn $char4.;
	format vendor $char5.;
   	format i 1.;
	format cnprpwrk $char2.;
   	format dist 2.;
	format bdtotal dollar15.2;
	format btuprice dollar12.2;
   	format cnprjnum $char30.;
	label cndtlet   = /OPEN/DATE/'------'
		cnpcn     = //KEY/'----'
	   	vendor    = /BDR/CODE/'------'
         	i         = /BDR/SEQ/'---'
	   	cnprpwrk  = /WORK/TYPE/'----'
         	dist      = //DIST/'----'
	   	bdtotal   = CONTRACT/THREE LOW BIDS/BID AMOUNT/'--------------'
	   	btuprice  = MOBILIZATION/THREE LOW BIDS/BID AMOUNT/'---------------'
         	mob       = MOB/% of/CONT/'----'
	   	cnprjnum  = /PROJECT/NUMBER/'----------'
	;

*** The TITLE statements allow the user to label the first  ***
*** few lines of the output.                                ***;

title1 'IDAHO TRANSPORTATION DEPARTMENT-CONTRACT ADMINISTRATION SYSTEM';
title2 'MOBILIZATION PERCENT BY PROJECT COST';
title3 'AVERAGE MOBILIZATION FROM 1/00 - 7/00';
title4 'MOB % = MOB AMOUNT BID * 100 / (TOTAL BID - MOB AMOUNT BID)';
title5;
run;