************************************************************ * AdHoc Program: Unbalanced bid report * * * * Greg Curson, BAMS/DSS, Indiana Department of * * Transportation * * * * * * Objective: A report to look at contracts for * * unbalanced bidding * * * * Description: Uses the median price of the vendor's * * UBP(Unit Bid Price). If less than %40 of the median * * price then flag U and color line yellow, If over * * %160 of the median price then flag A and color line * * orange, if greater than %160 median and is an item * * that INDOT considers front end loading then flag F * * and color line red * * U - Unbalanced - Yellow * * A - Above Tolerance - Orange * * F - Front Load - Red * * SAS style template was created for a PDF * * (would have to make changes to work for HTML) * * * * Input: Check mark Libraries:DB and VLAS(your view) * * db.dpropest * * VLAS * * dproposl * * dbidders * * dbidtabs * * vendor * * itemlist * * * * * * * * Output: Unbalanced Report * * d:\itidps\jobs\dss\bidreport.pdf * * * * Frequency Reports * * * * d:\itidps\jobs\dss\contractub.pdf * * d:\itidps\jobs\dss\Vendorub.pdf * * d:\itidps\jobs\dss\itemub.pdf * * * ************************************************************ ; options source source2 symbolgen; ******************Location of LAS view**********; libname lowbi 'd:\dssintf\vlas\test'; ******************DPROPOSL***************; data dproposlbid; set lowbi.dproposl (read=password); keep contid ccontid cndtlet ispecyr; run; proc sort data =dproposlbid out = bid1; by contid; run; *****************DBIDDERS*********************; data dbiddersbid; set lowbi.dbidders (read=password); keep contid vendor bdbstat bdtotal; run; proc sort data = dbiddersbid out = bid5; by contid vendor; run; ***********lowest bid***********************; proc sort data=bid5 out=tbid5; by contid bdtotal; run; data tcontid(keep=contid bdtotal rename=(bdtotal=lowest)); set tbid5; where not missing(bdtotal); by contid bdtotal; if first.contid; run; data minbdtotal2; merge tbid5 tcontid; by contid; run; *****************lowest vendor******************; data lowvend; set minbdtotal2; if lowest = bdtotal then lvendor = vendor; run; data low2vend(keep= contid lvendor); set lowvend; If lvendor NE ''; run; data minbdtotal3(keep= contid lowest lvendor); merge minbdtotal2 low2vend; by contid; run; *****************DBIDTABS*********************; data dbidtabsbid; set lowbi.dbidtabs (read=password); keep contid vendor iplineno item btuprice btoqty btextamt btlowcst; run; proc sort data = dbidtabsbid out = bid6; by vendor contid iplineno; run; ********************VENDOR****************************; data vendorbid; set lowbi.vendor (read=password); keep vendor vnames; run; proc sort data = vendorbid out = bid7; by vendor; run; *******************ITEMLIST*****************************; data itemlistbid; set lowbi.itemlist (read=password); keep item ispecyr idescr iunits itemclss; run; proc sort data = itemlistbid out = bid8; by item ispecyr ; run; ****************joining 1,6 and 5(Dproposl,Dbidders and Dbidtabs)**********************; proc sql; create table tjoin as select distinct bid6.*, bid5.*, bid1.* from bid6 left join bid5 on bid6.contid=bid5.contid and bid6.vendor=bid5.vendor left join bid1 on bid5.contid=bid1.contid ; ****************Adding Vendor Name to Min bids*******************; Proc sql; create table ven2 as select distinct minbdtotal3.*, bid7.* from minbdtotal3 left join bid7 on minbdtotal3.lvendor = bid7.vendor ; data ven3(rename=(vnames =lvenname)); set ven2; run; ************************Adding Vendor Names********************************; Proc sql; create table ven as select distinct tjoin.*, bid7.* from tjoin left join bid7 on tjoin.vendor = bid7.vendor ; *****************Adding Item Description*************************; Proc sql; create table item as select distinct ven.*, bid8.* from ven left join bid8 on ven.item = bid8.item and ven.ispecyr = bid8.ispecyr ; **********************Median********************************; proc sort data=item; by contid iplineno; run;; proc summary data=item nway; var Btextamt; by contid iplineno; output out=two (drop=_:) median=mediancost; run; data desired; merge item two; by contid iplineno; run; ******************Median Percent*********************************; data medianpercent; set desired; percentofmedian= btextamt/mediancost; run; ******************unbalanced Flag*********************************; ***********item Numbers below are INDOT front end loading items at this time******; data balflag; set medianpercent; if percentofmedian = <.40 then balflag= 'U'; if item in ('108-01614','110-01001','201-01015','201-01025','201-05821','201-0658','201-52370', '202-01000','202-02240','202-51328','202-51330','203-02000','203-02070','207-08262', '207-08263','207-08264','207-08265','207-08266','207-08267','207-08268','301-07448', '302-06464','303-01180','306-08033','306-08034','306-08036','306-08037','306-08038', '306-08039','306-08041','306-08042') and percentofmedian = >1.60 then balflag= 'F'; else if percentofmedian = >1.60 then balflag= 'A'; run; Proc sql; create table balflaglow as select distinct balflag.*, ven3.* from balflag left join ven3 on balflag.contid = ven3.contid ; ************creating correct estimate total*********; data estm2; set db.dpropest; keep contid cestamt aestamt est; format cestamt dollar15.2; informat cestamt dollar15.2; est = aestamt; if est = . then est = cestamt; format est dollar15.2; informat est dollar15.2; run; proc sort data = estm2; by contid; run; ************Joining estimate total**********************; Proc sql; create table balflag22 as select distinct balflaglow.*, estm2.* from balflaglow left join estm2 on balflaglow.contid = estm2.contid ; proc sort data = balflag22; by cndtlet contid iplineno; run; *************************formating fix********************; data fixest; set balflag22; newest = put(est,dollar15.2); newlowest = put(lowest,dollar15.2); run; proc contents data = fixest; run; **********************style of pdf report***********************; options nodate nonumber leftmargin=.3in rightmargin=.3in nobyline; ods path work.mytmplt(update) sashelp.tmplmst(read); proc template; delete styles.specstyl / store=work.mytmplt; run; proc sort data=fixest out=fixest; by descending cndtlet contid iplineno; *****************to get one contract*********; ********Where contid = "000ITI-MAD2"********; *******************run**********************; *****************************************************; ** make a style template to do the cyan title; ** need black box around title ONLY; ** need to set frame=void for table; ** want frame=box for systitleandfooter container; ** this is a copy of styles.printer, but with; ** changes for cyan title and trying for black box; ** around title. Also made some font changes; proc template; define style Styles.specstyl; parent = styles.default; replace fonts / 'TitleFont2' = ("Times Roman",11pt,Bold Italic) 'TitleFont' = ("Times Roman",11pt,Bold Italic) 'StrongFont' = ("Times Roman",10pt,Bold) 'EmphasisFont' = ("Times Roman",10pt,Italic) 'FixedEmphasisFont' = ("Courier",9pt,Italic) 'FixedStrongFont' = ("Courier",9pt,Bold) 'FixedHeadingFont' = ("Courier",9pt,Bold) 'BatchFixedFont' = ("SAS Monospace, Courier",6.7pt) 'FixedFont' = ("Courier",9pt) 'headingEmphasisFont' = ("Times Roman",11pt,Bold Italic) 'headingFont' = ("Times Roman",11pt,Bold) 'docFont' = ("Times Roman",9pt); replace GraphFonts / 'GraphDataFont' = ("Times Roman",8pt) 'GraphValueFont' = ("Times Roman",10pt) 'GraphLabelFont' = ("Times Roman",12pt,Bold) 'GraphFootnoteFont' = ("Times Roman",12pt,Bold) 'GraphTitleFont' = ("Times Roman",14pt,Bold); replace Table from Output / rules = NONE frame = VOID cellpadding = 3 cellspacing = 0; replace color_list "Colors used in the default style" / 'link' = blue 'bgH' = grayBB 'fg' = black 'bg' = _undef_; replace Body from Document "margins will come from printer or SYS option" / bottommargin = _undef_ topmargin = _undef_ rightmargin = _undef_ leftmargin = _undef_ pagebreakhtml = html('PageBreakLine'); replace colors "Abstract colors used in the default style" / 'headerfgemph' = color_list('fg') 'headerbgemph' = color_list('bgH') 'headerfgstrong' = color_list('fg') 'headerbgstrong' = color_list('bgH') 'headerfg' = color_list('fg') 'headerbg' = color_list('bgH') 'datafgemph' = color_list('fg') 'databgemph' = color_list('bg') 'datafgstrong' = color_list('fg') 'databgstrong' = color_list('bg') 'datafg' = color_list('fg') 'databg' = color_list('bg') 'batchbg' = color_list('bg') 'batchfg' = color_list('fg') 'tableborder' = color_list('fg') 'tablebg' = color_list('bg') 'notefg' = color_list('fg') 'notebg' = color_list('bg') 'bylinefg' = color_list('fg') 'bylinebg' = color_list('bg') 'captionfg' = color_list('fg') 'captionbg' = color_list('bg') 'proctitlefg' = color_list('fg') 'proctitlebg' = color_list('bg') 'titlefg' = color_list('fg') 'titlebg' = color_list('bg') 'systitlefg' = color_list('fg') 'systitlebg' = color_list('bg') 'Conentryfg' = color_list('fg') 'Confolderfg' = color_list('fg') 'Contitlefg' = color_list('fg') 'link2' = color_list('link') 'link1' = color_list('link') 'contentfg' = color_list('fg') 'contentbg' = color_list('bg') 'docfg' = color_list('fg') 'docbg' = color_list('bg'); replace GraphColors / 'gcerror' = cx000000 'gerror' = cxA0A0A0 'gcpredictlim' = cx000000 'gpredictlim' = cxC8C8C8 'gcpredict' = cx000000 'gpredict' = cx000000 'gcconfidence' = cx000000 'gconfidence' = cxC8C8C8 'gcfit' = cx000000 'gfit' = cx000000 'gcoutlier' = cx000000 'goutlier' = cxA0A0A0 'gcdata' = cx000000 'gdata' = cxA0A0A0 'ginsetheader' = colors('docbg') 'ginset' = cxFFFFFF 'greferencelines' = cxCDCDCD 'gheader' = colors('docbg') 'gconramp3cend' = cx5F5F5F 'gconramp3cneutral' = cxA7A7A7 'gconramp3cstart' = cxF0F0F0 'gramp3cend' = cx5F5F5F 'gramp3cneutral' = cxA7A7A7 'gramp3cstart' = cxF0F0F0 'gconramp2cend' = cx5F5F5F 'gconramp2cstart' = cxF0F0F0 'gramp2cend' = cx5F5F5F 'gramp2cstart' = cxF0F0F0 'gtext' = cx000000 'glabel' = cx000000 'gborderlines' = cx000000 'goutlines' = cx000000 'ggrid' = cxECECEC 'gaxis' = cx000000 'gshadow' = cx000000 'glegend' = cxFFFFFF 'gfloor' = cxCCCCCC 'gwalls' = cxFFFFFF 'gcdata12' = cx000000 'gcdata11' = cx000000 'gcdata10' = cx000000 'gcdata9' = cx000000 'gcdata8' = cx000000 'gcdata7' = cx000000 'gcdata6' = cx000000 'gcdata5' = cx000000 'gcdata4' = cx000000 'gcdata3' = cx000000 'gcdata2' = cx000000 'gcdata1' = cx000000 'gdata12' = CX2F2F2F 'gdata11' = CX6F6F6F 'gdata10' = CXCFCFCF 'gdata9' = CX8F8F8F 'gdata8' = CX4F4F4F 'gdata7' = CXAFAFAF 'gdata6' = CX0F0F0F 'gdata5' = CX7F7F7F 'gdata4' = CX3F3F3F 'gdata3' = CX9F9F9F 'gdata2' = CX5F5F5F 'gdata1' = CXBFBFBF; replace Container "Abstract." / font = Fonts('DocFont') foreground = colors('docfg') background = colors('docbg'); style ContentTitle from IndexTitle "Controls the title of the Contents file." / fillrulewidth = 0.5pt rightmargin = 1em leftmargin = 1em outputwidth = 100% bottommargin = 4ex topmargin = 3ex font = fonts('TitleFont') pretext = text('content title') just = C; replace PrintedContentsLabel "Sort of a post-posttext for the CONTENTS" / posttext = " (*ESC*){leaders . }(*ESC*){tocentrypage}" pretext = "(*ESC*){tocentryindent 2em}"; replace ContentItem from IndexItem "Controls the leafnode item in the Contents file." / rightmargin = 15% leftmargin = 15% just = L; replace ContentFolder from IndexItem "Controls the generic folder definition in the Contents file." / rightmargin = 15% leftmargin = 15% just = L listentryanchor = off foreground = colors('confolderfg'); replace ContentProcName from IndexProcName "Controls the proc name in the Contents file." / rightmargin = 15% leftmargin = 15% just = L; replace ContentProcLabel from ContentProcName "Controls the proc label in the Contents file." / posttext = _undef_ pretext = _undef_; replace Date from Container "Abstract. Controls how date fields look." / outputwidth = _undef_ background = colors('contentbg') foreground = colors('contentfg'); replace SysTitleAndFooterContainer from Container "Changed container" / background = #66ffcc foreground = black rules = NONE frame = Box outputwidth = 100% cellpadding = 3 cellspacing = 0 borderwidth = 3 bordercolor=black; replace TitleAndNoteContainer from Container "Changed container" / background = #66ffcc foreground = black rules = NONE frame = Box outputwidth = 100% cellpadding = 3 cellspacing = 0 borderwidth = 3 bordercolor=black; replace TitlesAndFooters from Container "Changed container" / background = #66ffcc foreground = black rules=NONE frame=box outputwidth=100% cellpadding=3 cellspacing=0 borderwidth=3 bordercolor=black; replace SystemTitle from TitlesAndFooters "Controls system title text." / font = Fonts('TitleFont') background=#66ffcc; replace SystemFooter from TitlesAndFooters "Controls system footer text." / font = Fonts('TitleFont') background=#66ffcc; end; run; ** start ods pdf and close listing; ** use style template from above; ods listing close; ** Name the pdf and the location of the file; ods pdf file='d:\itidps\jobs\dss\bidreport.pdf' style=Styles.specstyl uniform; ** will need ods escapechar for the title; ods escapechar='~'; ** use proc report style= overrides to make report changes; ** rules= none turns off interior table lines; ** frame= void turns off box around table; ** cellspacing= 0 turns off the space between each cell; ** cellpadding=3 minimizes the white space -inside- each cell; ** background= changes background color of cell; ** default for header cell is gray; ** foreground= changes foreground color of cell; ** just= l changes justification in a table cell; ** ~{thispage} is the PDF way to get page numbers onto; ** the report and ~n is the way to get a forced line break; ** after the word "Indiana" and ~_ is a non-breaking space; options linesize=256; proc report data=fixest nowd style(report)={rules=none frame=void cellspacing=0 cellpadding=3 just=l} style(header)={background=white just=c}; title1 j=l 'Letting Date: #byval(cndtlet)' j=r 'Page: ~{thispage}'; title2 j=l " " j=c 'Indiana~nDepartment~_of~_Transportation'; title3 j=l ' ' j=c 'Unbalanced~_Bid~_Report'; title4 j=l ' ' j=c "%sysfunc(today() ,worddate18.)"; title5 j=l 'Lowest Bidder: #byval(lvenname) ' j=r 'Contract Number: #byval(contid)'; title6 j=l 'Lowest Bid Amount: #byval(newlowest)' j=r 'INDOT Estimate Amount: #byval(newest)'; by descending cndtlet contid newest newlowest lvenname; format cndtlet worddate18.; column cndtlet iplineno item idescr iunits mediancost vnames btoqty btuprice btextamt Balflag; define cndtlet / group descending noprint; define iplineno / group noprint; define item / group noprint; define idescr / group noprint; define iunits / group noprint; define mediancost / group noprint; define vnames / display 'Vendors' left; define btoqty / display 'Quantity' center; define btuprice / display 'UBP' center f=dollar14.2 width =16; define btextamt / display 'Amount' center width =12; define Balflag / display 'Flag' center width =4; compute Balflag / character length=1; if balflag = 'U' then do; call define(_ROW_,'STYLE','style={background=yellow}'); end; if balflag = 'A' then do; call define(_ROW_,'STYLE','style={background=orange}'); end; if balflag = 'F' then do; call define(_ROW_,'STYLE','style={background=red}'); end; endcomp; break after cndtlet / ; compute after cndtlet; line @ 1 "Note: UBP is the Vendor's unit bid price. Median Price is the median of the UBP of each proposal line. If Flagged as 'U' and"; line "yellow, this is a bid of less than %40 of the median price. If Flagged as 'A' and orange, this is a bid of greater than %160 of"; line "the median price. And if Flagged as 'F' and red, this is a bid greater than %160 of the median price and is a front loaded item."; endcomp; ** next compute block does the gray bar; ** underneath the header and at the start of every ; ** iplineno. If some of this info is going to come; ** from macro vars will need double quotes and if; ** some of this info is coming from data, will need; ** to change column statement to include those vars; compute before mediancost / style={background=#cccccc just=l}; line 'Proposal:' iplineno $20. ' Item: ' item $13. ' ' idescr $40. ' Units: ' iunits $4. ' Median Price: ' mediancost dollar15.2; endcomp; ** this compute block skips a line after every iplineno; compute after mediancost; line ' '; endcomp; run; ods listing close; **********Contract Number Report ******************; ods pdf file='d:\itidps\jobs\dss\contractub.pdf' style=sasweb; title1'Contract Number'; title2'A - Above Tolerance F - Front Load U - Unbalanced '; proc freq data=fixest; tables contid*balflag; run; **********Vendor Report ******************; ods pdf file='d:\itidps\jobs\dss\Vendorub.pdf' style=sasweb; title1'Vendors'; title2'A - Above Tolerance F - Front Load U - Unbalanced '; proc freq data=fixest; tables vnames*balflag; run; **********Items Report ******************; ods pdf file='d:\itidps\jobs\dss\itemub.pdf' style=sasweb; title1'Items'; title2'A - Above Tolerance F - Front Load U - Unbalanced '; proc freq data=fixest; tables item*balflag; run; ods _all_ close; title; footnote; options byline;