libname aq "f:\aq\"; filename myfiles ("f:\aq\raw\detroit04.csv","f:\aq\raw\detroit05.csv","f:\aq\raw\detroit06.csv"); options linesize=255; options pagesize=255; title "Detroit AQ Analysis"; ********AQ_2012.SAS, designed to estimate individual and neighborhood-racial models for counties or metropolitan areas; ********Change the 'filename' statement above to obtain estimates for different regions; data aq.raw(compress=yes); infile myfiles delimiter="," missover; length respid $ 10; input year respid $ agency $ ltype prop $ lpurp occup lamt preapp $ actype msa $ state $ county $ tract $ apeth $ caeth $ aprace1 $ aprace2 $ aprace3 $ aprace4 $ aprace5 $ carace1 $ carace2 $ carace3 $ carace4 $ carace5 apsex casex apinc $ ptype $ den1 $ den2 $ den3 $ ratespr $ hoepa $ lien $ edit $ sequence $ pop $ minpct $ hudmfi $ trmsa $ ownocc $ sfunits $ apflag; run; data aq.full(compress=yes); set aq.raw; fail=0; if (edit='5' or edit='6' or edit='7') then fail=1; nstate=0; if (state ne 'NA' and state ne 'N/A' and state ne 'na' and state ne .) then nstate=state; *Note, some observations have state="NA", these observations will be coded nstate=0; nstate=0; nstate=state; ncounty=0; ncounty=county; ntract=0; ntract=tract; nfips=(nstate*10000000)+(ncounty*10000)+ntract; fips=state||county||tract; length tagent $1; length lendid $11; tagent=agency; lendid=tagent||respid; nmin_x=999; nmin_x=minpct; nmin_y=0; if nmin_x ge 50 then nmin_y=1; rate=.; if (ratespr ne 'na' and ratespr ne "NA") then rate=ratespr; hicost=0; if rate gt 0 then hicost=1; rvhicost=1; if rate gt 0 then rvhicost=0; napinc=0; if (apinc ne 'NA' and apinc ne 'na' and apinc ne 'N/A' and apinc ne .) then napinc=apinc*1000; napinc2=napinc*napinc; nlamt=(lamt*1000); vx1=0; if (prop ne '3') then vx1=1; label vx1="all sf records"; vx2=0; if (prop ne '3') and (ltype=2 or ltype=3 or ltype=4) then vx2=1; label vx2="all sf fha records"; vx3=0; if (prop ne '3') and (ltype=1) and (actype lt 6) then vx3=1; label vx3="sf conventional excl purch preapp"; vx4=0; if (prop ne '3') and (ltype=1) and (actype = 3) then vx4=1; label vx4="sf conventional denials"; vx5=0; if (prop ne '3') and (ltype=1) and (actype lt 6) and (lpurp=1) then vx5=1; label vx5="sf conventional hp apps"; vx6=0; if (prop ne '3') and (ltype=1) and (actype=1) and (lpurp=1) then vx6=1; label vx6="sf conventional hp origs"; vx7=0; if (prop ne '3') and (ltype=1) and (actype=1) and (lpurp=1) and hicost=1 then vx7=1; label vx7="hicost sf conventional hp origs"; vx8=0; if (prop ne '3') and (ltype=1) and (actype lt 6) and (lpurp=2) then vx8=1; label vx8="sf conventional hi apps"; vx9=0; if (prop ne '3') and (ltype=1) and (actype=1) and (lpurp=2) then vx9=1; label vx9="sf conventional hi orig"; vx10=0; if (prop ne '3') and (ltype=1) and (actype=1) and (lpurp=2) and hicost=1 then vx10=1; label vx10="hicost sf conventional hi origs"; vx11=0; if (prop ne '3') and (ltype=1) and (actype lt 6) and (lpurp=3) then vx11=1; label vx11="sf conventional refi apps"; vx12=0; if (prop ne '3') and (ltype=1) and (actype=1) and (lpurp=3) then vx12=1; label vx12="sf conventional refi origs"; vx13=0; if (prop ne '3') and (ltype=1) and (actype=1) and (lpurp=3) and hicost=1 then vx13=1; label vx13="hicost sf conventional refi origs"; vx14=0; if (prop = '2') then vx14=1; label vx14="all mobile home records"; vx15=0; if (prop ne '3') and (apeth='2') and (aprace1='3') then vx15=1; label vx15="nonhisp black records"; vx16=0; if (prop ne '3') and (apeth='1') then vx16=1; label vx16="hispanic records"; vx17=0; if (prop ne '3') and (apeth='2') and (aprace1='5') then vx17=1; label vx17="nonhispanic white records"; vx18=0; if (prop ne '3') and (apeth='2') and (aprace1='2') then vx18=1; label vx18="nonhispanic asian records"; vx19=0; if (prop ne '3') and (apeth='2') and (aprace1='1' or aprace1='4') then vx19=1; label vx19="nonhispanic native records"; vx20=0; if (prop ne '3') and (ltype=1) and (actype=1) and (apeth='2') and (aprace1='5') then vx20=1; label vx20="NH white sf originations"; vx21=0; if (prop ne '3') and (ltype=1) and (actype=1) and (apeth='2') and (aprace1='5') and (hicost=1) then vx21=1; label vx21="NH white hicost originations"; vx22=0; if (prop ne '3') and (ltype=1) and (actype=1) and (apeth='2') and (aprace1='3') then vx22=1; label vx22="NH black sf originations"; vx23=0; if (prop ne '3') and (ltype=1) and (actype=1) and (apeth='2') and (aprace1='3') and (hicost=1) then vx23=1; label vx23="NH black hicost originations"; vx24=0; if (prop ne '3') and (ltype=1) and (actype=1) and (apeth='1') then vx24=1; label vx24="Hispanic sf originations"; vx25=0; if (prop ne '3') and (ltype=1) and (actype=1) and (apeth='1') and (hicost=1) then vx25=1; label vx25="Hispanic hicost originations"; vx26=0; if (prop ne '3') and (agency='1' or agency='4' or agency='5') then vx26=1; label vx26="All SF Applications to Reason-required Lenders"; vx27=0; if (prop ne '3') and actype=3 and (agency='1' or agency='4' or agency='5') then vx27=1; label vx27="All Denials at Reason-Required Lenders"; vx28=0; if (prop ne '3') and actype=3 and (den1='3' or den2='3' or den3='3') and (agency='1' or agency='4' or agency='5') then vx28=1; label vx28="Bad-Credit Denials at Reason-Required Lenders"; vx29=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='1') and (apeth='2') and (aprace1='5') and (hicost=1) then vx29=1; label vx29="NH white hicost originations held in portfolio"; vx30=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='2' or ptype='3' or ptype='4') and (apeth='2') and (aprace1='5') and (hicost=1) then vx30=1; label vx30="NH white hicost originations sold to GSEs"; vx31=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') and (apeth='2') and (aprace1='5') and (hicost=1) then vx31=1; label vx31="NH white hicost originations sold to nonagency"; vx32=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='1') and (apeth='2') and (aprace1='3') and (hicost=1) then vx32=1; label vx32="NH black hicost originations held in portfolio"; vx33=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='2' or ptype='3' or ptype='4') and (apeth='2') and (aprace1='3') and (hicost=1) then vx33=1; label vx33="NH black hicost originations sold to GSEs"; vx34=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') and (apeth='2') and (aprace1='3') and (hicost=1) then vx34=1; label vx34="NH black hicost originations sold to nonagency"; vx35=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='1') and (apeth='1') and (hicost=1) then vx35=1; label vx35="Hispanic hicost originations held in portfolio"; vx36=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='2' or ptype='3' or ptype='4') and (apeth='1') and (hicost=1) then vx36=1; label vx36="Hispanic hicost originations sold to GSEs"; vx37=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') and (apeth='1') and (hicost=1) then vx37=1; label vx37="Hispanic hicost originations sold to nonagency"; vx38=0; if (prop ne '3') and (ltype=1) and (actype=1) and (apeth='3' or apeth='4' or aprace1='6' or aprace1='7') then vx38=1; label vx38="Race/ethnicity unknown sf originations"; vx39=0; if (prop ne '3') and (ltype=1) and (actype=1) and (apeth='3' or apeth='4' or aprace1='6' or aprace1='7') and (hicost=1) then vx39=1; label vx39="Race/ethnicity unknown hicost originations"; vx40=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='1') and (apeth='3' or apeth='4' or aprace1='6' or aprace1='7') and (hicost=1) then vx40=1; label vx40="Race/ethnicity unknown hicost originations held in portfolio"; vx41=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='2' or ptype='3' or ptype='4') and (apeth='3' or apeth='4' or aprace1='6' or aprace1='7') and (hicost=1) then vx40=1; label vx41="Race/ethnicity unknown hicost originations sold to GSEs"; vx42=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') and (apeth='3' or apeth='4' or aprace1='6' or aprace1='7') and (hicost=1) then vx40=1; label vx42="Race/ethnicity unknown hicost originations sold to nonagency"; vx43=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') and hicost=1 then vx43=1; label vx43="All hicost originations sold to nonagency"; vx44=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') and hicost=1 and nmin_y=1 then vx44=1; label vx44="Hicost originations in majority minority sold to nonagency"; vx45=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') and (hicost=1 and nmin_y=1) and ((apeth='2') and (aprace1='3') or apeth='1') then vx45=1; label vx45="Black & Hispanic Hicost originations in majority minority sold to nonagency"; vx46=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') and (hicost=1 and nmin_y=1) and ((apeth='2') and (aprace1='5')) then vx46=1; label vx46="Nonhispanic White Hicost originations in majority minority sold to nonagency"; vx47=0; if (prop ne '3') and (ltype=1) and (actype=1) and nmin_y=1 then vx47=1; label vx47="All Originations in Majority Minority neighborhoods"; vx48=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') then vx48=1; label vx48="All Originations Sold to Nonagency Purchasers"; vx49=.; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') and hicost=1 then vx49=nmin_x; label vx49="Tract minority percentage of Subprime Nonagency Purchases"; vx50=.; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') and hicost=1 and ((apeth='2') and (aprace1='3') or apeth='1') then vx50=nmin_x; label vx50="Tract minority percentage of Subprime Nonagency Purchases to Minority Individuals"; miss=0; if (prop ne '3') and (ltype=1) and (actype=1) and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') and hicost=1 and (aprace1='6' or minpct="NA") then miss=1; label miss="Hicost originations sold to nonagency with missing race and/or neighborhood race data"; rvcred1=1; if actype=3 and (den1='3' or den2='3' or den3='3') and (agency='1' or agency='4' or agency='5') then rvcred1=0; label rvcred1="rv dummy1 bad credit"; rvcred2=1; if actype=3 and (den1='3') and (agency='1' or agency='4' or agency='5') then rvcred2=0; label rvcred2="rv dummy2 bad credit"; x_orig=0; if actype=1 then x_orig=1; x_decl=0; if actype=2 then x_decl=1; x_deny=0; if actype=3 then x_deny=1; x_with=0; if actype=4 then x_with=1; x_clos=0; if actype=5 then x_clos=1; x_purc=0; if actype=6 then x_purc=1; lratio=napinc/nlamt; lratio2=lratio*lratio; *note that preapproval reporting is only required for certain 'covered' preapproval programs; *therefore, preapp can be 1, yes, 2, no, or 3, not applicable, if the lender does not have a 'covered'; *pre-approval procedure; xpre=0; if preapp='1' then xpre=1; *jumbo=0; *if lien='1' and nlamt gt 417000 and (nstate ne 02 and nstate ne 15) then jumbo=1; *if lien='1' and nlamt gt 625500 and (nstate=02 or nstate=15) then jumbo=1; *if lien='2' and nlamt gt 208500 and (nstate ne 02 and nstate ne 15) then jumbo=1; *if lien='2' and nlamt gt 312750 and (nstate=02 or nstate=15) then jumbo=1; *Note, demunk is coded 1 if any application information is unreported -- sex, race, or ethnicity; demunk=0; if (apeth='3' or apeth='4') or (aprace1='6' or aprace1='7') or (apsex=3 or apsex=4) then demunk=1; female=0; if apsex=2 then female=1; hispan=0; if apeth='1' then hispan=1; native=0; if (apeth='2' and aprace1='1') then native=1; asian=0; if (apeth='2' and (aprace1='2' or aprace1='4')) then asian=1; black=0; if (apeth='2' and aprace1='3') then black=1; xoccup=0; if occup=1 then xoccup=1; conv=0; if ltype=1 then conv=1; fha=0; if (ltype=2 or ltype=3 or ltype=4) then fha=1; lien2=0; if lien='2' then lien2=1; r_occ=0; if agency='1' then r_occ=1; r_fdc=0; if agency='3' then r_fdc=1; r_ots=0; if agency='4' then r_ots=1; r_ncu=0; if agency='5' then r_ncu=1; r_hud=0; if agency='7' then r_hud=1; *reference category for regulator is hud; imprv=0; if lpurp=2 then imprv=1; refin=0; if lpurp=3 then refin=1; s_port=0; if actype=1 and ptype='0' then s_port=1; s_gses=0; if actype=1 and ptype='1' or ptype='2' or ptype='3' or ptype='4' then s_gses=1; s_priv=0; if actype=1 and ptype='5' then s_priv=1; s_bank=0; if actype=1 and ptype='6' then s_bank=1; s_fcom=0; if actype=1 and ptype='7' then s_fcom=1; s_affl=0; if actype=1 and ptype='8' then s_affl=1; s_othr=0; if actype=1 and ptype='9' then s_othr=1; r_priv=1; if actype=1 and ptype='5' then r_priv=0; r_bank=1; if actype=1 and ptype='6' then r_bank=0; r_fcom=1; if actype=1 and ptype='7' then r_fcom=0; r_affl=1; if actype=1 and ptype='8' then r_affl=0; r_othr=1; if actype=1 and ptype='9' then r_othr=0; r_scale=1; if actype=1 and (ptype='5' or ptype='6' or ptype='7' or ptype='8' or ptype='9') then r_scale=0; label r_scale="loans sold to any non-agency purchaser"; length mincat $ 20; mincat="00. unknown"; if minpct ne . and minpct ne "NA" and minpct < 10 then mincat="01. Less than 10%"; if (10