libname kathe "f:\sasdat\kathe"; * NJShare.sas revised February 3, 2011 to include 2009 HMDA; * no other changes other than addition of 2009 data; options linesize=256; options pagesize=200; data kathe.nj04(compress=yes); infile "f:\sasdat\kathe\nj04.csv" 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; if (apinc ne "NA" and apflag=0 and state ne "NA" and county ne "NA" and tract ne "NA"); run; data kathe.nj05(compress=yes); infile "f:\sasdat\kathe\nj05.csv" 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 $; if (apinc ne "NA" and state ne "NA" and county ne "NA" and tract ne "NA"); run; data kathe.nj06(compress=yes); infile "f:\sasdat\kathe\nj06.csv" 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 $; if (apinc ne "NA" and state ne "NA" and county ne "NA" and tract ne "NA"); run; data kathe.nj07(compress=yes); infile "f:\sasdat\kathe\nj07.csv" 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 $; if (apinc ne "NA" and state ne "NA" and county ne "NA" and tract ne "NA"); run; data kathe.nj08(compress=yes); infile "f:\sasdat\kathe\nj08.csv" 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 $; if (apinc ne "NA" and state ne "NA" and county ne "NA" and tract ne "NA"); run; data kathe.nj09(compress=yes); infile "f:\sasdat\kathe\nj09.csv" 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 $; if (apinc ne "NA" and state ne "NA" and county ne "NA" and tract ne "NA"); run; data kathe.njshares(compress=yes); set kathe.nj04 kathe.nj05 kathe.nj06 kathe.nj07 kathe.nj08 kathe.nj09; napinc=0; napinc=apinc; nstate=0; nstate=state; ncounty=0; ncounty=county; ntract=0; ntract=tract; nfips=(nstate*10000000)+(ncounty*10000)+ntract; fips=state||county||tract; rate=.; if (ratespr ne 'na' and ratespr ne "NA") then rate=ratespr; trxmsa=.; if trmsa ne "NA" then trxmsa=trmsa; hicost=0; if rate gt 0 then hicost=1; rvhicost=1; if rate gt 0 then rvhicost=0; 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 (hicost=1) and (occup ne 1) and (napinc gt 150) then vx29=1; label vx29="Conventional hicost originations to non-owner occupied, income > 150k"; vx30=0; if (prop ne '3') and (ltype=1) and (actype=1) and (hicost=1) and (occup = 1) and (napinc lt 40) then vx30=1; label vx30="Conventional hicost originations to owner occupied, income < 40k"; run; proc means data=kathe.njshares noprint; class nfips; var vx1-vx30; output out=kathe.sums(compress=yes) sum=vx1-vx30; by year; run; filename xtable1 DDE "excel|sheet1!r1c1:r10000c40" notab; proc print data=kathe.sums; run; proc export data=kathe.sums replace dbms=TAB outfile=xtable1; run;